looking for a good csv import routine. Like most of you i must have
written this many times but don't have access to my server until the
12th and really don't have time to redo right now.

So if anyone out there has one and could send it to me I would be
greatly appreciative.

thanks

"dtsig" wrote:

Dave, I'm not sure if this will help but here is code for both sides
of the equation.
1) To convert an Excel workbook with multiple sheets into consistently
formed CSV files, see my ExcelExport freeware and related ReadMe info
on my website. This does not require mv.NET or any other licensed
controls.
http:// removethisNebula-RnD.com/freeware/
2) To convert the output from that program into MV data, here is a
sample proggie that converts rows to multivalued attributes. You can
use the functional lines in a loop after you have read a CSV sheet:
*
LINE = \"cell","a4","has","embedded ""quote"" marks","see?"\
LINE = SWAP(LINE,\""\,@SVM) ; * temp change for real quotes
LINE = SWAP(LINE,\","\,@VM) ; * columns = values
LINE = LINE[2,LEN(LINE)-2] ; * remove leading/trailing quotes
LINE = SWAP(LINE,@SVM,\"\) ; * restore quotes to data
CRT LINE
*

Hope that helps,
Tony
TG@ yes,yes,removethisNebula-RnD.com

Hello Tony,

what about:
LINE1 = \cell,a4,has,embedded "quote" marks,see?\
LINE2 = \"cell","a4","has","embedded ""quote"" marks",2\
LINE3 = \"cell";"a4";"has";"embedded ""quote"" marks";"see?"\
LINE4 = \cell;a4;has;embedded "quote" marks;see?;2\

Regards,
Grigory

Try this:

01 subroutine csv.parser(string0,rec)
02 * 10-19-06 asb
03 * parse comma separated value record watching out for quotes ("")
04 * attribute delimited rec is returned
05
06 $options ext
07
08 string = string0
09 rec = ""
10 an = 1
11 loop
12 if string[1,1] ne '"' then
13 result = field(string,",",1)
14 string = string[col2()+1,999999]
15 end else
16 pos = index(string,'",',1)
17 if pos eq 0 then pos = len(string)
18 result = string[2,pos-2]
19 string = string[pos+2,999999]
20 end
21 result = change(result,'""','"')
22 rec<an> = trim(result)
23 until string eq "" do
24 an +=1
25 repeat
26
27 return

Here is a test:

in: "123,456.78",abcd,123.45,"this, is the ""real"" thing","abc",123""
out: 123,456.78^abcd^123.45^this, is the "real" thing^abc^123"

/Scott Ballinger
Pareto Corporation
Edmonds WA USA
206 713 6006

Grigory wrote:
Have you ever heard the phrase "don't look a gift horse in the mouth?"

Those ambiguous examples would not be generated by my ExcelExport
program. If you start with crap for data then you'll need a lot of
complex code to parse it. Rather than write code that anticipates
problems, I eliminated most or all problems in the source data by
using consistent structure - the end result is that only four lines of
code is required for parsing. Scott's code handles some
inconsistencies but it won't handle others. However, it's a good
start and was generously offered for free.

I'm sure our colleagues would like to see your free code to reformat
all possible permutations of poorly structured data. Personally, if
someone needs a parser for data that is that bad, I bill for my time.
If you really want code to parse your examples, you can pay me first.

T

Hello Tony,

Excuse me, please. My example is just for attention that different
content makes different result. I just want to tell that each line
should be parsed byte-by-byte. Certainly, Your way is the fastest and
most simple.

Additional problem is: " ""text1"",""text2"" in one
att",".........."

*Program My_Gift_Horse
SrcLine= 'A1,A2(2),"a3",""A4"","A5,A5(2)","A6
""qouted1"",""qouted2""",A7,8'
DstArray=""
Delimiter=","
IgnoreErrors=1
LineLen=LEN(SrcLine)
FOR I=1 TO LineLen
IF I>1 THEN DstArray:=@AM
NewAtt=""
IF SrcLine[i,1]='"' THEN
LOOP
i+=1
NewAttTmp=FIELD(SrcLine[i,LineLen],'"',1)
i+=LEN(NewAttTmp)+1
NewAtt:=NewAttTmp
IF SrcLine[i,1]='"' THEN
NewAtt:='"'
END ELSE
NextI=INDEX(SrcLine[i,LineLen],Delimiter,1)-1
IF IgnoreErrors AND NextI>1 THEN
NewAtt:='"':SrcLine[i,NextI-1]
i+=NextI
IF SrcLine[i,1]#Delimiter THEN i+=LineLen
EXIT
END
REPEAT
END ELSE
NewAtt=FIELD(SrcLine[i,LineLen],Delimiter,1)
i+=LEN(NewAtt)
END
DstArray:=NewAtt
NEXT I

CRT DstArray
END


You can look in the mouth of my_gift_horse. I'll be grateful for any
comments. Your comments will helpful for all.

Healthy teeth it is a healthy horse.

Regards,
Grigory

Tony Gravagno wrote:
Thanks tony .. i'll take a look

Scott Ballinger wrote:
thanks man .. looks like it will work from the get go. will parse it
through and if i find any weak spots will let you know for your use.

DTSig

GVP wrote:
Thanks very much Grigory, very interesting process

DTsig

Hi

There are no problems. Ten minutes for entertainment.
Many thanks. I'll add this code into BCPTOSRV program.

Regards,

Grigory