Problem with CSV format

ceaser · Feb 8, 2009

Hi Forum

I have a problem! :sign0085:Normally a "csv" file will look as follows:

ABC,12000,14000,120.023,Steel Peg

This is very easy to import with the "StrSplit" command. But when I use the "Table to CSV" export function, the file looks as follows:

ABC,"12,000","14,000",120.023,Steel Peg

Any number>999 the function puts a comma in and encapulates the number with "

How would I import something like that??

Please HELP:sign0085::sign0085:

Thanks
Michael

klaus · Feb 8, 2009

Hi Miachael,

Unfortunately we are missing some more information.

But I suspect that in your table you have cString columns and you use the Format keyword to enter the numbers.

In the attached program there are two tables, Table1 with cNumber columns and Table2 with cString columns for the numbers and formatting the number display.

The csv file for Table1 is as you expect it.
The csv file for Table2 looks like yours.

Best regards.

ceaser · Feb 8, 2009

Hi Klaus:sign0188:

Es klappt noch immer nicht!!

I am attaching a part of my code:

B4X:

Sub ExportDesign
   CreateTables(Job)
   SaveDialog1.Filter = "CSV Files|*.csv"
   SaveDialog1.Show
   If SaveDialog1.File<>"" Then
      Table2.Clear
      If Checkbox1.Checked=True Then
         SaveDialog1.File=SubString(SaveDialog1.File,0,StrLength(SaveDialog1.File)-4) & "1.csv"
         Table2.Clear
         cmd.CommandText = "Select * From HorPi "
         cmd.ExecuteTable("table2",0)
         Table2.SaveCSV (SaveDialog1.File, "," ,True)
      End If
      If Checkbox2.Checked=True Then
         'Align Pts
         SaveDialog1.File=SubString(SaveDialog1.File,0,StrLength(SaveDialog1.File)-4) & "2.csv"
         Table2.Clear
         cmd.CommandText = "Select * From Align "
         cmd.ExecuteTable("table2",0)
         Table2.SaveCSV(SaveDialog1.File, "," ,True)
      End If
      If Checkbox3.Checked=True Then
         'Ver Pi's
         SaveDialog1.File=SubString(SaveDialog1.File,0,StrLength(SaveDialog1.File)-4) & "3.csv"
         Table2.Clear
         cmd.CommandText = "Select * From VerPi "
         cmd.ExecuteTable("table2",0)
         Table2.SaveCSV (SaveDialog1.File, "," ,True)
      End If
      If Checkbox4.Checked=True Then
         'Crossfalls
         SaveDialog1.File=SubString(SaveDialog1.File,0,StrLength(SaveDialog1.File)-4) & "4.csv"
         Table2.Clear
         cmd.CommandText = "Select * From Crossfall "
         cmd.ExecuteTable("table2",0)
         Table2.SaveCSV (SaveDialog1.File, "," ,True)
      End If
      If Checkbox5.Checked=True Then

This is then what it gives me when I open it in "Notepad":

No,StartSv,YCoord,XCoord,Radius,Length,Code,Stake
1,0.000,"-40,082.690","385,083.320",0.000,0.000,0.000,0.000
2,0.000,"-44,995.990","382,877.007",0.000,0.000,0.000,"5,385.938"
3,0.000,"-45,228.949","382,747.253","-1,550.000",0.000,0.100,"5,652.926"
4,0.000,"-49,440.756","379,900.831",0.000,0.000,0.000,"10,736.373"
5,0.000,"-49,746.831","379,767.152",920.000,0.000,0.100,"11,072.228"
6,0.000,"-51,101.950","379,450.927",0.000,0.000,0.000,"12,463.755"
7,0.000,"-51,510.280","379,235.347",-910.000,0.000,0.100,"12,930.603"
8,0.000,"-54,400.511","376,584.240",0.000,0.000,0.000,"16,852.567"
9,0.000,"-54,550.433","376,425.099","-1,500.000",0.000,0.100,"17,071.398"
10,0.000,"-56,626.729","373,871.328",0.000,0.000,0.000,"20,362.713"
11,0.000,"-56,686.455","373,790.206","-1,050.000",0.000,0.100,"20,463.489"
12,0.000,"-59,268.767","369,905.438",0.000,0.000,0.000,"25,128.224"
13,0.000,"-59,339.582","369,807.680","1,500.000",0.000,0.100,"25,248.969"
14,0.000,"-61,061.104","367,621.994",0.000,0.000,0.000,"28,031.210"
15,0.000,"-61,296.386","367,383.055","1,520.000",0.000,0.100,"28,367.228"
16,0.000,"-64,274.008","364,962.455",0.000,0.000,0.000,"32,204.614"
17,0.000,"-64,571.398","364,522.596",-917.000,0.000,0.100,"32,743.285"
18,0.000,"-65,562.655","361,327.111",0.000,0.000,0.000,"36,088.985"
19,0.000,"-65,608.789","361,202.156","1,260.000",0.000,0.100,"36,222.247"
20,0.000,"-66,645.981","358,793.123",0.000,0.000,0.000,"38,845.071"
21,0.000,"-66,775.081","358,510.687","7,000.000",0.000,0.100,"39,155.640"
22,0.000,"-67,846.240","356,298.437",0.000,0.000,0.000,"41,613.572"

The above represents a horizontal alignment, which is stored in a SQLite database. If you look at the above, you will notice that as soon as the value is bigger than 999.99, it encapulates the number with ". Everything smaller than 1000 is OK.

Please help.:sign0085:

Regards
Michael

ceaser · Feb 8, 2009

Hi Klaus

As a another example, I am attaching the file of my road widths:

Stake,LeftWidth,RightWidth
0.000,6.100,6.100
"9,350.000",6.100,6.100
"9,465.000",8.400,6.100
"14,380.000",8.400,6.100
"14,400.000",6.100,6.100
"14,500.000",6.100,6.100
"14,520.000",6.100,8.400
"15,795.000",6.100,8.400
"15,910.000",6.100,6.100
"16,190.000",6.100,6.100
"16,305.000",8.400,6.100
"17,330.000",8.400,6.100
"17,350.000",6.100,6.100
"23,870.000",6.100,6.100
"23,985.000",8.400,6.100
"26,420.000",8.400,6.100
"26,440.000",6.100,6.100
"26,586.650",6.100,6.100
"26,738.100",6.100,9.130
"26,792.900",7.868,10.225
"26,803.200",8.200,10.225
"26,843.600",8.200,10.225
"26,933.600",6.100,10.225
"27,017.400",6.100,10.225
"27,120.525",6.100,6.100
"31,070.000",6.100,6.100
"31,120.000",7.068,6.100
"31,140.000",7.450,8.400
"31,185.000",8.400,8.400
"33,320.000",8.400,8.400
"33,340.000",6.100,8.400
"33,410.000",6.100,8.400
"33,525.000",6.100,6.100
"34,970.000",6.100,6.100
"35,085.000",8.400,6.100
"36,580.000",8.400,6.100
"36,600.000",6.100,6.100
"38,470.000",6.100,6.100
"38,490.000",6.100,8.400
"39,930.000",6.100,8.400
"40,045.000",6.100,6.100
"41,550.000",6.100,6.100

Again, the widths are OK (numeric), but the Stake Values along the route which are bigger than 999.99 are encapulated in ".

Regards
Michael

klaus · Feb 8, 2009

Hi Michael,

I am not a SQL specialist, but how are thenumbers stored in the data base? In a string field or a numeric field.
Do you initialize the table columns before loading the data base? If yes are is the column type cString or cNumber?
I am afraid that the problem is in the data base and not in the table. I tried in the test program to enter the numbers in cString columns, but commenting out the lines with the Format keywords, and the csv file is OK.

Best regards.

ceaser · Feb 8, 2009

Hi Klaus

Sorry for being a pain in your backside!:sign0013: But I need to resolve this problem.

Here is how I save my alignment files:

B4X:

Sub SaveHorPi_Click
   If Textbox1.Text="" Then Textbox1.Text=Format(0,"N3")
   If Textbox4.Text="" Then Textbox4.Text=Format(0,"N3")
   If Textbox5.Text="" Then Textbox5.Text=Format(0,"N3")
   If Textbox6.Text="" Then Textbox6.Text=Format(0,"N3")
   If Textbox2.Text<>"" AND Textbox3.Text<>"" Then
      If Code=1 Then
         rec=rec+1
         text="insert into HorPi (No,StartSv,PiYCoord,PiXCoord,Radius,TransIn,TransOut) values ('"
         text=text & rec & "','" & Textbox1.Text & "','" & Textbox2.Text & "','" & Textbox3.Text & "','" & Textbox4.Text & "','" & Textbox5.Text & "','" & Textbox6.Text & "')"
         cmd.CommandText=text
         cmd.ExecuteNonQuery   
      Else If Code=3 Then
         CheckAlign
         If Engine.j<>-999999 Then
            rec=rec+1
            text="insert into Align (No,StartSv,YCoord,XCoord,Radius,Length,Code,Stake) values ('"
            text=text & rec & "','" & Textbox1.Text & "','" & Textbox2.Text & "','" & Textbox3.Text & "','" & Textbox4.Text & "','" & Textbox5.Text & "','" & Textbox6.Text & "','" & Textbox7.Text & "')"
            cmd.CommandText=text
            cmd.ExecuteNonQuery
         End If
      End If
      Textbox2.Focus
   End If
End Sub

All the values get entered into "Textboxes"

This is how I open the database:

B4X:

Sub OpenHorPI
   Con.New1
   Reader.New1
   DirCreate("Data")
    Cmd.New1("",con.value)
   Con.Open("Data Source = " & AppPath & "\Data\" & Main.Job & ".sl3")
   If Code=1 Then
      cmd.New1("CREATE TABLE IF NOT EXISTS HorPi (No,StartSv,PiYCoord,PiXCoord,Radius,TransIn,TransOut)",con.value)
   Else If Code=3 Then
      cmd.New1("CREATE TABLE IF NOT EXISTS Align (No,StartSv,YCoord,XCoord,Radius,Length,Code,Stake)",con.value)
   End If
   cmd.ExecuteNonQuery
   If Code=1 Then
      cmd.CommandText="Select * From HorPi "
   Else If Code=3 Then
      cmd.CommandText="Select * From Align "
   End If
   Reader.Value = Cmd.ExecuteReader
   Combobox9.Clear
   Do While Reader.ReadNextRow=True
      rec=rec+1
      Combobox9.Add(Reader.GetValue(0))
   Loop
   reader.close
End Sub

Logic (I think I have some!) tells me that should my data be strings, then surely the numbers smaller than 1000 should also have had " around them. But they do not have them!

It is only for number => 1000 which have them..i.e. <1000 = 999.99, but >=1000 = "1,000.00" Does the "comma" not have anything to do with it?

Regards
Michael

agraham · Feb 8, 2009

It is always easier if you can post some example code otherwise we have to try and build forms etc to see what is happening and it wastes a lot of time!

I suspect the problem is to do with numbers being returned as strings and any >999 being formatted with commas on return from the query. SaveTable appears to save anything it recognises as a valid number without quotes, even if it is in a cString column. Because the commas make it a non-valid number such numbers are saved as quoted strings. Can you see the commas in the table entries?

Did you specify the columns in the Table control you used to create SQLite table as cNumber?

klaus · Feb 8, 2009

Hi Michael,

When you enter the values into the TextBoxes do you also use something like TextBox2.Text=Format(AnyNumber,"N3") ?
If yes I think that's the problem, the Format instruction introduces a comma every thousands. For example 1234567.34523 becomes 1,234,567.345 !
Are the numbers in the database put into number fields or string fields.
I modifyed the test program with 2 TextBoxes where I enter 2 numbers with Format. In Table1 with cNumber columns the text from the TextBox is converted to a number without the comma and the csv file is also OK. But in Table2 with cString columns the numbers become strings and they are displayed and transfered as those with the commas.

Best regards.

ceaser · Feb 10, 2009

Hi Klaus:sign0188:

Thank you very much for your help. My problem has been solved!

Sometimes I cannot see the wood in the forest!

Regards
Michael

Problem with CSV format

ceaser

Active Member

klaus

Expert

Attachments

ceaser

Active Member

ceaser

Active Member

klaus

Expert

ceaser

Active Member

agraham

Expert

klaus

Expert

Attachments

ceaser

Active Member

Problem with CSV format

Active Member

Expert

Attachments

Active Member

Active Member

Expert

Active Member

Expert

Expert

Attachments

Active Member

Privacy & Transparency

Privacy & Transparency