Problem with CSV format

Discussion in 'Questions (Windows Mobile)' started by ceaser, Feb 8, 2009.

  1. ceaser

    ceaser Active Member Licensed User

    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 ":confused:

    How would I import something like that??:confused:

    Please HELP:sign0085::sign0085:

    Thanks
    Michael
     
  2. klaus

    klaus Expert Licensed User

    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.
     

    Attached Files:

  3. ceaser

    ceaser Active Member Licensed User

    Hi Klaus:sign0188:

    Es klappt noch immer nicht!!

    I am attaching a part of my code:

    Code:
    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.:confused:

    Please help.:sign0085:

    Regards
    Michael
     
  4. ceaser

    ceaser Active Member Licensed User

    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
     
  5. klaus

    klaus Expert Licensed User

    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.​
     
  6. ceaser

    ceaser Active Member Licensed User

    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:

    Code:
    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:

    Code:
    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!:confused:

    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
     
  7. agraham

    agraham Expert Licensed User

    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?
     
  8. klaus

    klaus Expert Licensed User

    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.
     

    Attached Files:

  9. ceaser

    ceaser Active Member Licensed User

    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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice