CODEWITHSUNDEEP
excelcsvvbscript
Nico
Nico

Reputation: 15

Decimalseperator lost after conversion from csv to excel with vb-script

I have a CSV with semicolon seperators that I would like to convert to a regular Excel sheet. I managed to do this with the code below, but I must have made a mistake because numbers with decimals in the original file that don't start with a zero are shown in Excel as number without the decimal separator. When I open the CSV manually in Excel the result will be fine, so it must be a side-effect of doing it with a script.

For example:

In the CSV there is a line:

2013-03-10 17:00:15; idle; 2,272298;; 0,121860

In the Excel sheet this becomes:

2013-03-10 17:00 | idle | 2.272.298| | 0,121860

Opened manually in excel gives:

2013-03-10 17:00 | idle | 2,272298| | 0,121860

Could somebody please tell me what I could/should change to keep the decimals as decimals in Excel? Possibly a way to tell Excel which symbol represents the decimal separator or an argument to force it into using European formats?

Kind regards, Nico

This is the script I currently have, where csvFile is a string with the full path to the original file and excelFile is a string with the full path to the location where I want to store the new excel sheet. 

Set objExcel = CreateObject("Excel.Application")                   'use excel  
objExcel.Visible = true                                            'visible  
objExcel.displayalerts=false                                       'no warnings 

objExcel.Workbooks.Open(csvFile)                                   'open the file  
objExcel.ActiveWorkbook.SaveAs excelFile, -4143, , , False, False  'save as xls  
objExcel.Quit                                                      'close excel

Upvotes: 0

Views: 789

Answers (4)

Nico
Nico

Reputation: 15

I made a work around. I now create a copy of the CSV file where I replace all commas followed by a number by points. While not very effective it does give Excel what it wants and it is simple enough for an inexperienced programmer like me to use. When doing so a college asked me to also remove white spaces and entries with duplicate values in the first column (the timestamp in this case).

The result was this script

    'csvFile   is a string with the full path to the file. e.g. "C:\\Program Files\\Program\\data.csv"
    'tempFile  is a string with the full path to the file. e.g. "C:\\Temp\\temp.csv"
    'excelfile is a string with the full path to the file. e.g. "D:\\Data\\sheet.xls"

    Set fs=CreateObject("Scripting.FileSystemObject")
    Set writeFile = fs.CreateTextFile(tempFile,True)
    Set readFile = fs.OpenTextFile(csvFile)

    ' regular expression to remove leading whitespaces
    Set regular_expression = New RegExp
        regular_expression.Pattern = "^\s*"
        regular_expression.Multiline = False

    ' regular expression to change the decimal seperator into a point        
    Set regular_expression2 = New RegExp
        regular_expression2.Global = True
        regular_expression2.Pattern = ",(?=\d)"
        regular_expression2.Multiline = False

    'copy the original file to the temp file and apply the changes
    Do Until readFile.AtEndOfStream
        strLine= readFile.ReadLine
        If (StrComp(current_timestamp,Mid(strLine, 1, InStr(strLine,";")),1)<>0) Then
        If (Len(previous_line) > 2) Then
            previous_line = regular_expression2.replace(previous_line,".")
            writeFile.Write regular_expression.Replace(previous_line, "") & vbCrLf
        End if
        End if
        current_timestamp = Mid(strLine, 1, InStr(strLine,";"))
        previous_line = strLine
    Loop
    readFile.Close
    writeFile.Close

    Set objExcel = CreateObject("Excel.Application")    ' use excel
    objExcel.Visible = true                             ' visible
    objExcel.displayalerts=false                        ' no warning pop-ups
    objExcel.Workbooks.Open(tempFile)                   ' open the file
    objExcel.ActiveWorkbook.SaveAs excelfile, -4143, , , False, False   'save as excelfile
    fs.DeleteFile tempFile                              ' clean up the temp file

I hope this will also be useful for someone else.

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200233

In general Excel honors the system's regional settings. The CSV import, however, sometimes has its own mind about the "correct" format, particularly when the imported file has the extension .csv.

I'd try the following. Rename the file to .txt or .tsv and import it like this:

objExcel.Workbooks.OpenText csvFile, , , 1, 1, False, False, True

Upvotes: 0

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

Create a schema.ini file in the folder your csvFile lives in and describe it according to the rules given here.

Further reading: import, text files

Upvotes: 1

K_B
K_B

Reputation: 3678

There are several approaches possible, I will cover one that I favor:

  1. Start Recording a macro
  2. Create a new workbook
  3. From that workbook go to Data > From Text and there you select the CSV file, then you can do all the required settings regarding Value separators, Decimal separators, Thousands separators. Also the specific data type can be selected for each column.
  4. When the CSV content is added go to Data > Connections and Remove the connection. The data will stay in the worksheet, but there is no longer an active connection.
  5. Save the workbook under the xls name
  6. Stop the Recording

Now tweak the script a bit to your liking.

Upvotes: 0

Related Questions