import problem 1

[fluppe689]

Hey all Experts,

I ha a problem importing a csv file

the layout is the following

Naam,Email,Bestelnummer,Kunstgras,Type kunstgras,Verbruik per m2,Type kunstgras (b),Verbruik per m2 (b),Accessoires,Type accessoire,Verbruik,Type accessoire 2,Verbruik 2,Type accessoire 3,Verbruik 3,Type accessoire 4,Verbruik 4,Type accessoire 5,Verbruik 5,Type accessoire 6,Verbruik 6,Type accessoire 7,Verbruik 7,Type accessoire 8,Verbruik 8,Type accessoire 9,Verbruik 9,Type accessoire 10,Verbruik 10,Type accessoire 11,Verbruik 11,Type accessoire 12,Verbruik 12,Type accessoire 13,Verbruik 13,Type accessoire 14,Verbruik 14,Type accessoire 15,Verbruik 15,Werkuren,Vertrek Merelbeke,Einde werf,Aantal man ,Betaling,Betaling oke?,Overige,Referentie? ,Foto's genomen?,Opmerkingen,Hoeveel kokers bij klant?

Casters,,4514,,PLAY,"488,8",,,,GEO,"488,8",ZAND,3paletten,LIJMBAND,40,LIJMKOKER1KG,5,,,,,,,,,,,,,,,,,,,,,,,,8u,13u,2,,,,,1,Afwerking voorjaar 2013,0

Every record is separated by 0D 0D 0A

the problem is that I cannot say that the delimiter is a comma because the values between "" is a numeric value

I really don't know how to start on it
I cannot change the csv file because it comes from an app on IPAD


Can anyone help met a bit please

 

[Olaf Doschke]

I think you will have a problem with field naames containinng "?", spaaces and digits.

But you just have got the meaning wrong about DELLIMITER. You can very well set comma as the field seperator, if you set " as delimiter, a comma inside string delimiters is then not taken as a field seperator, but of course the field is read in as a string, numbers need a decimal point, so you need to do a two staged import anyway.

The delimiter in the APPEND FROM command is meaning string delimiter (if used) and the WITH CHARACTER clause is where you put the field seperator, so it's

APPEND FROM yourcsv.txt DELIMITED WITH " WITH CHARACTER ,

Bye, Olaf.

 

[Olaf Doschke]

A simple example:

StrToFile(["123,234",abc,2000],"yourcsv.txt")
Create Cursor curTest (c1 C(10), c2 c(10), c3 c(10))
APPEND FROM yourcsv.txt DELIMITED WITH " WITH CHARACTER ,

This demonstrates how delimiters keep the comma in the field, and how they are optional (as abc also is read in as a string).

Bye, Olaf.

 

[Mike Lewis]

Have you tried going to Regional Settings in Control Panel, and changing the decimal symbol to a comma? I don't know if that will solve the problem (it's probably already a comma), but it would be the first thing to check.

Failing that, you say you can't change the file because it comes from an iPad. That doesn't follow. Once you get the file in your system, you can do anything you like with it. So, one possibility would be to parse it for strings occuring between double quotes, and for each such string, change the comma to a point. Then proceed with the import as usual.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike, that's system setting for the decimal point is a default in german windows (and other locales), and it doesn't change how APPEND handles values delimited with ", they always are strings anyway. SET POINT changes how VAL() works on such strings, but I would rather EVAL(CHRTRAN(cNumber,",.",".")) in case you have german formatted numbers like 1.000,00 instead of 1.000,00 as UK or US would format them. The CHRTRAN turns a comma into the decimal point EVAL needs and removes the commas seperating each 3 digits of numnbers >1000.

Bye, Olaf.

 

[Mike Lewis]

that's system setting for the decimal point is a default in german windows (and other locales), and it doesn't change how APPEND handles values delimited with ", they always are strings anyway.

I wasn't sure about APPEND, but I'm fairly sure the regional settings affect output with COPY TO or EXPORT, so I thought it might be the same with importing. Also, you can use regional settings to change the list separator (a semi-colon by default in German and Dutch?), which has no VFP equivalent.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy