Member Avatar for odysseous

Hi,
I have a problem with reading a csv file with SQL server

UNH+ATOPHOEXEDI+AVLABL'
PLD+AII+RE11S'
TRV+1+40+DU03+MAN+LCA+20110706:0000:1'
ACC+1+SUNNY HILL HTL APTS ?+ POOL 3*:11113+:: PAPHOS+20110706+3:3 STAR++E:1:999'
PRC+A:825:3*C:0:1:15+9+5:2475:9:1BDR APT 2/4 INLAND VIEW+2:4++SC: SELF CATERING'
TRV+2+40+DU03+MAN+LCA+20110706:0000:7:N:20110713:0000'
ACC+2+SUNNY HILL HTL APTS ?+ POOL 3*:11113+:: PAPHOS+20110706+3:3 STAR++E:1:999'
PRC+A:4900:3*C:0:1:15+9+5:14700:9:1BDR APT 2/4 INLAND VIEW+2:4++SC: SELF CATERING'
.
.

ROWTERMINATOR is ' and fieldterminator +.
I have problem with column size.

Thank you

Edited by odysseous because: n/a
  • 2 Contributors
  • 1 Reply
  • 224 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by BitBlt
Member Avatar for BitBlt

I can see why you have problems. To begin with, it appears that the rows are of several different formats. In order to be able to parse all that you need to read row by row, make some logic choices and grouping breaks, and decide where each of the data in each row should wind up.

If you have some say in how the data is delivered to you, you might request that the deliverer give you the data in XML format. Then you can import it rather easily with OPENROWSET into a table with an XML column and use OPENXML to get the data out and put it in whatever relational tables you want.

If you have no control over how the data is delivered to you, I recommend you use a procedural programming language such as VB, or C/C++, or Java (depending on what you're most comfortable with) to read the file and build your logic that way. Trying to parse this using only TransactSQL or BCP would be pretty difficult.

Hope this helps!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.