[WD12] Importing a CSV

Hi guys,

Does anyone have a nice slick bit of code for importing a CSV file or some idea how I can do the following?

I need to import a CSV file... for example;

The header (1st) row is (no quotes):
Prod Code, Desc, SRP

The data rows (2nd row onwards) is (with quotes):
"123456","abc, ertyuiop","10.99"

I've tried ExtractString and it seems to work fine if your seperator is a single character not enclosed in double quotes.

Also, can't use HImportText as the import file isn't the same structure as the destination file.

I just want to be sure there's not a REALLY simple way of doing this that I'm missing and I certainly don't want to "re-invent the wheel" by writing some whiz-bang code unless I REALL have to..

Any ideas will be greatly received...

Thanks in advance.




Edited 2 time(s). Last edit at 12/16/2009 12:34AM by DarrenF.

Hello Darren,

Yes you can use hImporttext. I use it to fill 45 different files with data that all have different structures.

Put the data in a temp file then move it were it needs to go.


DW

Darren,
because your text may contain the separator as well (comma) you have to extent this separator. With double quotes in this case.
AND add it as a prefix/suffix to your lines, during import
"123456","abc, ertyuiop","10.99"
will then look like
"."123456","abc, ertyuiop","10.99","


sLine = ""," + "123456","abc, ertyuiop","10.99" + ",""
ExctractString(sLine,"","",firstRank)

Arie - I think you're right - making the line look as you say above and then making the separator equal to "," should work. Do you know if setting a string equal to "," (the separator) then using that string in ExtractString would work? Something like:

sLine = ""," + "123456","abc, ertyuiop","10.99" + ",""
sSep = ""","""
ExctractString(sLine,firstRank,sSep)

Thanks DW, HImportText is definately an option, however, due to the amount of coding I'd have to do, I'll have to consider the alternatives before I go down that route. The thing that complicates this solution for me is that I don't know the structure of the CSV file(s), as each one could be different (as stated above).

A bit of background to this is that I'm developing a user definable Excel and CSV data import/loader as I don't want to have to write a new program each time a new supplier offers a product load (import file) and at the moment there are a dozen or so suppliers and I certainly don't want to have to write 12 and then maintain them when they change and they WILL change

The Excel files aren't a problem, but the CSV files could be in any format, some don't have column headers, some have the data enclosed in " marks, some are just comma delimited without quotes and I'm sure at some point a supplier will offer one with a different separator :eek:

Thanks for your suggestions... it's much appreciated :spos:

Darren, I did a small test. It's even easier.
Just remove the first and last double-quote of your line, instead of adding the whole separator. Or else you the firstRank will be empty.

This will work.

gsLine is string = fLoadText("c:\test.txt")
gsLine = Middle(gsLine,2,Length(gsLine)-2)
gsSSep is string = ""","""
Info(ExtractString(gsLine,firstRank,gsSSep) ) // says: 123456
Info(ExtractString(gsLine,nextRank,gsSSep) ) // says: abc, ertyuiop
Info(ExtractString(gsLine,nextRank,gsSSep) ) // says: 10.99

Hello Darren

We have a similar problem and instead of writing for a number of formats, we have trained our users. We ask them to open the data in Excel and to nominate the columns they want to read in by naming those columns with specific names that we have provided to them. (ProductCode, Description,ListPrice and so on) They then save that file and we read it in using the Windev Excel commands and we only read data from the named columns.

Regards
Al

Hi Arie,

Thanks for that, but I think it's going to be a combination of your suggestions.

My CSV file(s) can have 1,000 and sometimes as many as 15,000 records in them, so I'm thinking that I'll have to read each record and then do the ExtractString on each record - with your examples and suggestions, I'm sure I'll work it out :spos:

As always thanks very much for taking the time...

Hi Al,

There-in lies another few problems - you've opened a "can of worms" there

It's already quite complex for them as the data arrives in many different ways and many formats! As well as those formats already discussed (above), some send it on a DVD, some via email attachments others provide direct downloads.

I've always got to have in mind that these people aren't IT people they are shop owners and they are experts in selling furniture, bicycles etc. ...but I DO agree they have to take responsibility to a certain extent and it's my job to gague their expetations as much as possible :rp:

This means that they already have to locate the files, get them into an accessable area on PC they are about to process them on, go into the application, select them via a file selector in the app etc...etc...

Thanks for contributing... :spos:

Hi Darren

Put this procedure as Global Procedure and call it as the extractString, this procedure will decode CVS from Excel directly, without editting, including commas inside quotes, etc

     - No escape code for (") quote character though.
     - The only character used as separator is a comma.

Feel free to add functionallity and re-publish.


PS. I had the same problem, so I coded a while back.
WD 14 included CVSToArray and ArrayToCSV functions, but I haven´t tested them yet.

Andres Sanchez



PROCEDURE extractStringCSV(CVSString is string,nIndex is int)
sResultString, sUnString are string
x1,x2,x3,x4,x5 are int

sUnString = CVSString
LOOP (nIndex)
     sResultString = ""
     x1 = Position(sUnString,",")
     IF x1 = 0 THEN x1=Length(sUnString)+1
     x2 = Position(sUnString,"""")
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF x2=0 OR x1<x2 THEN
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sResultString = sUnString[[1 TO x1-1]]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sUnString = sUnString[[x1+1 TO ]]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x3 = Position(sUnString,""",",x2+1,FromBeginning)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF x3=0 THEN x3 = Length(sUnString)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sResultString=sUnString[[x2+1 TO x3-1]]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sUnString=sUnString[[x3+2 TO]]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LOOP
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x4 = Position(sResultString,"""""")
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF x4 = 0 THEN BREAK
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sResultString = sResultString[[1 TO x4]] + sResultString[[x4+2 TO ]]
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END
END
IF sResultString="" THEN sResultString=EOT

RESULT sResultString




Edited 2 time(s). Last edit at 12/16/2009 08:43PM by Andres Sanchez.

Hi Andreas,

Thanks for this...

Looks like a handy little utility :spos:

I also noticed the CSVtoArray functions - looks like they are in WD12 as well.