Re: Basic Program to Import a CSV file

Joe/Rob - problem with both of those is if there are commas in the data
- csv encapsultes a cell with quotes if there is a comma in there (or
sometimes each time regardless. Also if you then have quotes arround
each field, if you want a quote in the field it then puts a double
qoute in !!

csv is more than just a string of comma delimited data, there are a few
standards you need to comply to in order to do it properly -

here is a snippet of the kind of code i usually do to grab the data out
of a csv string ( you need to do the openseq and readseq and write to
uv file arround this code) :-
Spos=1
lp=1
Line=delim:Line ;* Always start with a comma (or whatever) !!
Line.len=LEN(Line)
Record=''
LOOP UNTIL Spos>=Line.len
NextDelim=delim
IF Line[Spos+1,1]=NextDelim THEN
Record<lp>='' ;* Null field
Epos=Spos+1
END ELSE
BEGIN CASE
CASE Line[Spos+1,1]='"'; NextDelim='"';Spos+=1
CASE Line[Spos+2,1]='"'; NextDelim='"';Spos+=2
CASE Line[Spos+1,1]="'"; NextDelim="'";Spos+=1
* CASE Line[Spos+2,1]="'"; NextDelim="'";Spos+=2
END CASE
IF Line[Spos+1,1]=NextDelim THEN
Record<lp>='' ;* Null field
Epos=Spos+1
END ELSE
Spos+=1;Offset=0;Offset.Count=0
LOOP

Epos=Spos+Offset+INDEX(Line[Spos+Offset,Line.len],NextDelim,1)-1
IF Epos=Spos-1 THEN
IF NextDelim=delim THEN
Epos=Line.len+1
END ELSE
Err='ERROR Unmatched ':NextDelim:' found near char
':Spos:' of file ':FileName:', Offset=':Offset:', Epos=':Epos
EXIT
END
END
* Check to see if this field has quotes inside it
* if it does it is enclosed in single quotes (") and each
quote inside is doubled ("")
IF Line[Epos,2]='""' THEN
Offset.Count+=2
Offset=Epos+Offset.Count
CONTINUE
END
Record<lp>=TRIM(Line[Spos,(Epos-Spos)]) ;* Excludes char on
Epos
EXIT
REPEAT
IF Err#'' THEN EXIT
END
END
oldSpos=Spos
Spos=Epos ;* This is the char that Nextdelim is. if this is not a
comma then we need to move up to the comma.
IF Line[Spos,1]#delim AND Spos<Line.len THEN
TMP=Spos+INDEX(Line[Spos,Line.len],delim,1)-1
IF TMP-Spos>2 THEN
* IF greater than 2 characters then do a warning message but continue
*Err='WARNING large gap found between delims for Delim ':NextDelim:'
end delim pos is ':Spos:' on line ':Line.count:' of file ':SeqFIL.name
END
Spos=TMP
END

lp+=1
REPEAT

Joe wrote:
Rob, almost but... ;) The OP indicated that the first value in each row
should be the item-id. How about something like this:

OPENSEQ CSVFILE TO SEQIN ELSE STOP
OPEN "DEALS.MV" TO DEALS ELSE STOP
LOOP
READSEQ LINE FROM SEQIN ELSE EXIT
CONVERT "," TO @AM IN LINE
ID = LINE<1> ; * 1st value in line
WRITE LINE ON DEALS,ID
REPEAT

Of course, the details should be filled in, such as the value for CSVFILE,
what to do if files aren't opened, any data conversion as you say, and the
possibility of duplicate keys.

Also, there is the potential issue that a comma may be actually part of
the data as opposed to a delimiter If this is a possibility and the
number of fields in each line is known, code like this could be used:

NUMBER.OF.FIELDS.THAT.SHOULD.BE.IN.THE.RECORD = 5 ; * from the OP
COMMA.COUNT = COUNT(LINE,",")
IF COMMA.COUNT # NUMBER.OF.FIELDS.THAT.SHOULD.BE.IN.THE.RECORD THEN
check for quotes around data and deal with it accordingly.
END

If data containing commas isn't consistently quoted properly, all data
integrity bets are off.

Regards,
Joe


"rcamarda" <robc390@xxxxxxxxxxx> wrote in news:1149765752.189460.51960
@y43g2000cwc.googlegroups.com:

* THIS works in universe for unix. Check OPENSEQ and change the file
location
*

OPENSEQ '/xfer/file.txt' TO SEQIN ELSE

* is suspect for windows you would us 'c:\dir\file.txt\' instead

CRT 'CANT OPEN SEQ FILE'

STOP

END

OPEN 'DEALS.MV' TO DEALS ELSE

CRT 'CANT OPEN DEALS.MV'

STOP

END

C=1

*

LOOP

READSEQ LINE FROM SEQIN ELSE EXIT

CONVERT ',' TO @AM IN LINE

* do any data conversion here before write

WRITE LINE ON DEALS, C

C += 1

REPEAT

END


HTH
Rob


.

Relevant Pages

  • Re: Your opinion please, embedded commas in CSV files
    ... A colleague is developing an import routine that reads data from a CSV ... The data field is surrounded by quotes only if it has an embedded comma, ...
    (microsoft.public.vb.general.discussion)
  • Re: OpenNETCF.Data.Csv - Limitations?
    ... item is treated as a single entry after which it expects a comma, ... any control over the data export into CSV, then if you can replace quotes ... within text to single quotes then you may have more luck. ... Windows Embedded MVP ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: getting data with proper encoding to the finish
    ... > so I used csv module to create CSV file and then import it in Excel. ... It quotes tabs, ... have only a comma in them. ...
    (comp.lang.python)
  • Re: Where to start?
    ... On Sun, 15 Feb 2004, Joe Peschel wrote: ... of what to do with literal quotes in the quoted material (the usual ... if it does underlining of book titles, it does not underline the comma). ... So Usenet idiom follows the same convention for // and **. ...
    (sci.crypt)
  • Re: Where to start?
    ... I agree that small local papers and web pages have quite a few mistakes. ... > of what to do with literal quotes in the quoted material (the usual ... > italics (or, if it does underlining of book titles, it does not ... > underline the comma). ...
    (sci.crypt)