Mixed Data Type

[Ronnie]

Hi - I am trying to read data from a file with mixed data type:

1, "this is number, one", 3, "This is more data, with more, commas", 7
17,"Data, with severa1 commas, and more", 0, "ten", 2

I tried to read the data as a lineinput and parse the information, but it did not parse.  Also tried the wordl$ command but since the size of the data changes it did not work.  Tried to delimte the data with tabs, and the inputto$ command with the same bad results.

Any help or suggestions will be appreciated.  Thank you...

[Rod]

Have you tried the new Liberty BASIC input command INPUTCSV. This reads comma delimited files. There are always little tweaks needed but try the command then let us know what is not working.

[Chris Iverson]

Hmm, interesting. It looks like INPUTCSV fails to read that exact example properly due to the spaces after some of the commas, and before the open quotation marks.

If the spaces are there, LB ignores the quotation mark and will parse the comma inside the embedded string like it's a new field. If the spaces are removed, INPUTCSV behaves as expected.

Here's the test code I created to check this. (Modify the file name as you see fit)
   open "C:\temp\test.txt" for input as #file

   for x = 1 to 3
       inputcsv #file, one$, two$, three$, four$, five$
       print one$
       print two$
       print three$
       print four$
       print five$
       print
   next x

   close #file

Here's my modified test data, with field headers for use later:

Field1, Field2, Field3, Field4, Field5
1, "this is number, one", 3, "This is more data, with more, commas", 7
17,"Data, with severa1 commas, and more", 0, "ten", 2

Here's the result from that test data:

Field1
Field2
Field3
Field4
Field5

1
"this is number
one"
3
"This is more data

with more
commas"
7
17
Data, with severa1 commas, and more

As you can see, it's breaking strings up across fields.

Here's the test data again, modified to remove leading commas:

Field1,Field2,Field3,Field4,Field5
1,"this is number, one",3,"This is more data, with more, commas",7
17,"Data, with severa1 commas, and more",0,"ten",2

Here's the output of that set, which is what is expected:

Field1
Field2
Field3
Field4
Field5

1
this is number, one
3
This is more data, with more, commas
7

17
Data, with severa1 commas, and more
0
ten
2


LB should be ignoring the leading whitespace when checking for quotes. At least, that's how .NET/powershell does it:
PS C:\temp> Import-Csv test.txt | Format-Table

Field1 Field2                              Field3 Field4                               Field5
------ ------                              ------ ------                               ------
1      this is number, one                 3      This is more data, with more, commas 7
17     Data, with severa1 commas, and more 0      ten                                  2

This was done with the file before I removed the whitespace. You can see it split the data up among the fields properly.

[tsh73]

Here's code from old forum (marked 2008)
I just inserted TRIM$ to fix that space issue.
'based on example by JanetTerra
'the goal is to convert that CSV line (separated with comma, probably with "," inside quoted field)
'to line separated with "|", de-quoted, to be worked with by WORD
'(ignore possibility that | could be inside of field. Or change it to say ".")
' -- ignore for now
   'Open "tsh.txt" for Input as #2
   Open "tst2.txt" for Input as #2
       lineN=0
       while not(eof(#2))
           Line input #2, aLine$
           lineN=lineN+1
           print lineN;" >>";aLine$ ;"<<"
           resStr$ = convertCSV2pipe$(aLine$)
           print ">";resStr$ ;"<"
           gosub [printByPipe]
       wend
   Close #2

end

[printByPipe]
'now, check
   item$="."
   i = 1
   while 1
       item$ = word$(resStr$, i, "|")
       if item$="" then exit while
       'and empty item returns as separator, so
       if item$ = "|" then  item$ = ""
       print i;  ">";item$;"<"
       'print i;  ">";deQuote$(item$);"<"
       i = i+1
   wend
return

'------------------------------
function convertCSV2pipe$(aLine$)
   ct = 0
   item = 1
   resStr$ = ""
   While Word$(aLine$, ct + 1, ",") <> ""
       'FileItem$ = Word$(aLine$, ct + 1, ",")
       FileItem$ = trim$(Word$(aLine$, ct + 1, ","))
       While Left$(FileItem$, 1) = Chr$(34) and _
           Right$(FileItem$, 1) <> Chr$(34)
               ct = ct + 1
               FileItem$ = FileItem$ + "," + Word$(aLine$, ct + 1, ",")
       Wend
       'Print item, FileItem$
       'resStr$ = resStr$ +"|"+ FileItem$
       resStr$ = resStr$ +"|"+ deQuote$(FileItem$)
       item = item + 1
       ct = ct + 1
   Wend
   resStr$ = mid$(resStr$, 2)  'trim first "|"
   convertCSV2pipe$ =  resStr$
end function
'------------------------------
function deQuote$(aStr$)
   b = 1
   e = len(aStr$)
   if mid$(aStr$, 1, 1) = chr$(34) then b = 2
   if mid$(aStr$, e, 1) = chr$(34) and e>=2 then e = e-1
   deQuote$ = mid$(aStr$, b, e-b+1 )
end function
'------------------------------