Export excel to csv-Retain number format to Text

Question

I'm using this code from here to Transposed convert excel to csv file. It's working perfect (Thanks to Nat).

Now I'm facing an issue : as I want to read from this file to fill out Autocad table, I need to csv number format to be as "Text" format(now it's "General" format which is normally happening when it opens with excel). When I import my csv data, it omits leading zero cells and changing 1:100 to 0.01.

When I open csv file with excel and change those cells format to "Text", then save and close file, it's working fine. How can I automate this process (or save with Text format in the first place) as I don't want each user do this manually.

Thanks

Private Sub Exporttocsv()
     Dim ColNum As Integer
      Dim Line As String
      Dim LineValues() As Variant
      Dim OutputFileNum As Integer
      Dim PathName As String
      Dim RowNum As Integer
      Dim SheetValues() As Variant

      PathName = Application.ActiveWorkbook.Path
      OutputFileNum = FreeFile

      Open PathName & "\Test.csv" For Output Lock Write As #OutputFileNum

      SheetValues = Sheets("Current Revision").Range("B2:CV98").value

    Dim RowMax
    RowMax = UBound(SheetValues)
    Dim ColMax
    ColMax = 99
    ReDim LineValues(1 To RowMax)

      For ColNum = 1 To ColMax
        For RowNum = 1 To RowMax
          LineValues(RowNum) = SheetValues(RowNum, ColNum)
        Next
        Line = Join(LineValues, ",")
        Print #OutputFileNum, Line
      Next
      Close OutputFileNum
    End Sub

Probably a simple change of `SheetValues = Sheets("Current Revision").Range("B2:CV98").value` to `SheetValues = Sheets("Current Revision").Range("B2:CV98").Text` will achieve what you want. — YowE3K, Oct 21 '17 at 08:09
Also, in order to check what the `csv` file looks like, you need to open it in something like `Notepad`. If you just `open` the file in Excel, there may be some unwanted conversions. — Ron Rosenfeld, Oct 21 '17 at 14:24
I don't understand exactly what the issue is. Are you actually importing the csv into AutoCAD? And is AutoCAD complaining because the leading zeroes are missing, and the ratios (e.g. 1:100) are being converted to decimals (0.01)? Or is AutoCAD complaining because there *are* leading zeroes and ratios? Also, in the last paragraph, do you mean that if you open the csv file in Excel, save it, and then import it in AutoCAD, AutoCAD doesn't complain? — robinCTS, Oct 21 '17 at 14:34
robinCTS, I'm actually importing the csv into AutoCAD and the ratios (e.g. 1:100) are being converted to decimals (0.01) and leading zero (e.g. 002) converted to 2. No complaining feom AutoCAD. If I open the csv file in Excel, save it, and then import it in AutoCAD,yes it's work fine. Alternatively to tweak the main code, I need a macro to open csv and change cells' format to Text and overwrite and save it. Hope that make sense. Thanks — Matt, Oct 21 '17 at 21:41
I also tried to change them in my main excel file to "=""01:100""" or "="1:100" but when convert excel to csv, they comes as original (i.e "=""01:100""") in csv. When I open csv with excel, cells showing 1:100 but with notepad( or correct way to see csv) they're not right. — Matt, Oct 21 '17 at 21:47

score 1 · Accepted Answer · answered Oct 22 '17 at 10:14

Verify that the CSV contains the values as you want them (e.g. 1:100 instead of 0.01), using any text viewer/editor like notepad, etc.

If values aren't formatted correctly, then to format them as text:

With Sheets("Current Revision").Range("B2:CV98")
.numberformat = "@"
.entirecolumn.autofit 'To avoid #### errors as we're using text property below
SheetValues = .text
End with

The above should replace this line in your original code.

SheetValues = Sheets("Current Revision").Range("B2:CV98").value

The alternative would be to modify the values in the array to, before printing to text file.

Untested and written on mobile, sorry for bad formatting. Hope it works.

score 0 · Answer 2 · answered Oct 22 '17 at 09:04

For writing the 1:100 ratio as is, into CSV, best is to use the type conversion when you are writing values in to the CSV file. like below :

      LineValues(RowNum) = CStr(SheetValues(RowNum, ColNum))

However, if you want leading Zeros, i dont think the type conversion also retains the leading zeros.

Export excel to csv-Retain number format to Text

2 Answers2