+ Reply to Thread
Results 1 to 7 of 7

formatting with leading zero and point in value

  1. #1
    Registered User
    Join Date
    08-24-2009
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    4

    formatting with leading zero and point in value

    I have a csv-file with the following content :
    "column 1";"column 2";"column 3"
    "column 1";01.0000;"column 3"

    I have a problem with the 2nd column. In excel I want to see the same value. But if i import this csv-file into an excelsheet programmatically with querytable, the value of the second column is converted to 10.000. Putting quotes around the value in the csv-file gives the same result of 10.000.

    I tried to create a custom format but did not succeed. I thought the format 00.0000 would do the job for me, but it converts the value to 010.000. I got my leading zero, but the point is at the wrong place. Probably because excels interpret it as a decimal point, but it isn't.

    If I open the csv-file in excel with use of the "text importing wizard", in the 3rd step of the wizard I mark the 2nd column as text. This gives me the wanted result in excel. My big question is how can I do this programmatically?

    Can anyone help me?

    TIA,
    Johan Beumer

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: formatting with leading zero and point in value

    Have you tried delimiting the csv (which I presume you can edit at source) with
    commas -> ,
    not semicolons -> ;
    ?

  3. #3
    Registered User
    Join Date
    08-24-2009
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: formatting with leading zero and point in value

    Just did, but same wrong result.

  4. #4
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: formatting with leading zero and point in value

    Perhaps upload an example?

  5. #5
    Registered User
    Join Date
    08-24-2009
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: formatting with leading zero and point in value

    You can create your own example I guess.

    Copy, paste and save this to a csv-file :
    "column 1";"column 2";"column 3"
    "column 1";"01.0000";"column 3"

    Double click the csv-file and the value of 01.0000 in the csv is shown as 10.000 in excel. That's my problem.

    Change the file extention to .txt. Start excel. Choose "open" and select the txt-file. The "text importing wizard" is started. In step 3 you can change the data-type of the columns (at least I can). Change the data-type of the 2nd column to "text" and the value in the excelsheet is shown as expected.

    I have automated this proces and obviously it gives me the same wrong result as manually. For now the code I use it not important, as the result with manually doing things is already wrong.

  6. #6
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: formatting with leading zero and point in value

    Quote Originally Posted by JohanB View Post
    Double click the csv-file and the value of 01.0000 in the csv is shown as 10.000 in excel. That's my problem.
    I get 1

    Quote Originally Posted by JohanB View Post
    Change the file extention to .txt. Start excel. Choose "open" and select the txt-file. The "text importing wizard" is started. In step 3 you can change the data-type of the columns (at least I can). Change the data-type of the 2nd column to "text" and the value in the excelsheet is shown as expected.
    No, this is a bad thing to do.

    Check your regional settings in the computer control panel, and as stated, use commas to delimit comma-separated-values files.

    If you want more help upload an example; as stated, I get 1.

    CC

  7. #7
    Registered User
    Join Date
    08-24-2009
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: formatting with leading zero and point in value

    Quote Originally Posted by Cheeky Charlie View Post
    No, this is a bad thing to do
    Why is it a bad thing to do? Besides that, it was just to show that excel can do what I want.

    Quote Originally Posted by Cheeky Charlie View Post
    Check your regional settings in the computer control panel
    Mine are Dutch. Changing it to US also gives me 1. But what have they got to do with it? They matter for numbers, but the value shouldn't be interpreted as a number in the first place.

    Quote Originally Posted by Cheeky Charlie View Post
    use commas to delimit comma-separated-values files.
    I changed it. Why should it be comma-separated instead of semicolon-separated?

    Quote Originally Posted by Cheeky Charlie View Post
    If you want more help upload an example
    Here´s my code, written in Progress 4GL :

    define variable chExcelApplication as com-handle no-undo.
    define variable chWorkbook as com-handle no-undo.
    define variable chWorksheet as com-handle no-undo.
    define variable chQueryTable as com-handle no-undo.

    define variable cConnection as character no-undo.
    define variable lResult as logical no-undo.
    define variable cFilesToImport as character no-undo.
    define variable cSheetTitles as character no-undo.
    define variable iNumberOfFiles as integer no-undo.

    create "Excel.Application" chExcelApplication.
    chExcelApplication:Workbooks:add.

    assign cFilesToImport = "K:\csv-test\Export_Kostencodes.txt"
    cSheetTitles = "Title 1"
    chWorkbook = chExcelApplication:WorkBooks:item(1).

    do iNumberOfFiles = 1 to num-entries(cFilesToImport).

    assign file-info:file-name = entry(iNumberOfFiles, cFilesToImport)
    cConnection = "TEXT;" + file-info:full-pathname
    chWorkSheet = chExcelApplication:Sheets:item(iNumberOfFiles)
    chWorkSheet:name = entry(iNumberOfFiles, cSheetTitles)
    lResult = chWorkSheet:QueryTables:add(cConnection, chWorkSheet:cells(1,1)).

    assign chQueryTable = chWorkSheet:QueryTables(1)
    chQueryTable:FieldNames = true
    chQueryTable:RowNumbers = false
    chQueryTable:FillAdjacentFormulas = false
    chQueryTable:PreserveFormatting = false
    chQueryTable:RefreshOnFileOpen = false
    chQueryTable:RefreshStyle = 1
    chQueryTable:SavePassword = false
    chQueryTable:SaveData = true
    chQueryTable:AdjustColumnWidth = true
    chQueryTable:RefreshPeriod = 0
    chQueryTable:TextFilePromptOnRefresh = false
    chQueryTable:TextFilePlatform = 437
    chQueryTable:TextFileStartRow = 1
    chQueryTable:TextFileParseType = 1
    chQueryTable:TextFileTextQualifier = 1
    chQueryTable:TextFileConsecutiveDelimiter = false
    chQueryTable:TextFileTabDelimiter = false
    chQueryTable:TextFileSemicolonDelimiter = false
    chQueryTable:TextFileCommaDelimiter = true
    chQueryTable:TextFileSpaceDelimiter = false
    chQueryTable:TextFileTrailingMinusNumbers = true
    lResult = chQueryTable:refresh
    chQueryTable:BackgroundQuery = false.
    end.

    /* Make Spreadsheet Visible */
    chExcelApplication:visible = true.

    /* Release All Objects */
    release object chQueryTable no-error.
    release object chWorkSheet no-error.
    release object chWorkBook no-error.
    release object chExcelApplication no-error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1