CSV quotes and DATA
BlitzPlus Forums/BlitzPlus Beginners Area/CSV quotes and DATA
| ||
Hello to all. Sorry this is not really a Blitz problem, but maybe someone can help. I'm making a quiz game and I want to export a lot of Excel data to CSV and ultimately make it a DATA statement, but because 90% of the CSV file is strings, I need each entry to have "quotes" around it and wondered if anyone knew a fast way to do this? Ive tried doing a Search and replace, e.g. replace the commas with "," but this gets messy because many of the strings contain commas and the last part of the data contains numbers which I dont want as strings. Here's an example... I have this in CSV: This is a question,answer1,answer2,1,2 and I want: Data "This is a question","answer1","answer2",1,2 I just want to avoid going through it line by line if possible. Any help greatly appreciated. |
| ||
do you have access to perl? I think you can get it for free its an open source product perl is great at doing this kind of stuff, if you do I can write the script for you. |
| ||
also if you wanted to use blizplus I think what you are trying to do is very similiar to the example posted here in the documentation section for removing tabs (except you are adding quotes) Function StripTabs$(cl$) pos=0 Repeat pos=pos+1 istab=0 l$=Left$(cl$,1) If Asc(l$)=9 Or Asc(l$)=32 istab=1 cl$=Right$(cl$,Len(cl$)-1) End If Until istab=0 Return Trim$(cl$) End Function |
| ||
Yes, I would do it in the same way, only I would use Instr to find the comma's. I don't think it matters if you quote the numeric values too though: you can still read them as integers. |
| ||
Assuming there are no stray spaces...newLine$ = chr$(34) + Replace(oldLine$, ",", chr$(34) + ", " + chr$(34)) + chr$(34) newLine$ = "Data " + Replace(newLine$, chr$(34) + chr$(34), chr$(34))?? |
| ||
Thanks very much for the suggestions guys! Bryan970, I do have access to Perl (if memory serves me correctly), but I will try the examples posted first. :) b32 and Yan, thanks for your code snippets, I can see the potential and will give it a try. Paulo |
| ||
Here's another way to go about it. It's a function from the code archives http://www.blitzbasic.com/codearcs/codearcs.php?code=1922 ;Parse string function demo ; By:Andy Amaya ;Date:2007.02.11 ;Purpose for parse$() function: ; To allow user to extract words or groups of symbols using ; the delimiter best suited to the task at hand. crLf$ = Chr$(13)+Chr$(10) separator$ = crLf$+"==========================================================="+crLf$ ;=================================================== ;Parse string using the "comma" character ; Possible Use: read data in comma separated value file ;=================================================== Print "...separating CSV fields" For x = 1 To 5 Print parse$("This is a question,answer1,answer2,1,2",x,",") Next Print separator$ Print "Creating data statement(s)..."+crLf$ lineOfData$ = "Data " For x = 1 To 5 If x < 4 Then lineOfData$ = lineOfData$ + Chr$(34) + parse$("This is a question,answer1,answer2,1,2",x,",") + Chr$(34) + "," Else lineOfData$ = lineOfData$ + parse$("This is a question,answer1,answer2,1,2",x,",") + "," End If Next lineLen = Len(lineOfData$) lineOfData$ = Left$(lineOfData$,lineLen-1) Print lineOfData$ Print separator$ a$ = Input("Press [ENTER] to Exit.") End Function parse$(string2Chk$, n, delimiter$=" ") ;initialize local variables Local count% = 0 Local findDelimiter% = 0 Local position% = 1 Local current$ = "" ;'n' must be greater than zero ;otherwise exit function and return null string If n > 0 Then ;strip leading and trailing spaces string2Chk$ = Trim(string2Chk$) ;find the word(s) Repeat ;first check if the delimiter occurs in string2Chk$ findDelimiter% = Instr(string2Chk$,delimiter$,position) If findDelimiter <> 0 Then ;extract current word in string2Chk$ current$ = Mid$(string2Chk$,position,findDelimiter-position) ;word extracted; increment counter count = count + 1 ;update the start position of the next pass position = findDelimiter + 1 ;if counter is same as n then exit loop If count = n Then findDelimiter = 0 End If Until findDelimiter = 0 ;Special Case: only one word and no delimiter(s) or last word in string2Chk$ If (count < n) And (position <= Len(string2Chk$)) Then current$ = Mid$(string2Chk$,position, Len(string2Chk$) - position+1) count = count + 1 ;looking for word that is beyond length of string2Chk$ If count < n Then current$ = "" End If End If Return current$ End Function |