Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA .csv converts some of my text fields to dates

Posted on 2012-08-16
10
Medium Priority
?
207 Views
Last Modified: 2013-02-14
When exporting an excel worksheet to .csv using vba I notice that some of my data gets converted to dates.  For example Part: 5-4026 is the value is excel but May-26 is the value that appears in the csv export.  Is there any way to prevent this from happening?
0
Comment
Question by:ka8799
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38302085
Force it to use text instead of general.  It might be as simple as formating the cells "text".

The code below is what I use for CSV exports.  It came from a Microsoft KB originally.  And it treats everything as text, and it inserts the "'s around all the cells!

you'll notice it uses selection.text, this forces the process to export the value as a text preventing coversion to a date.

-SA

Sub CSVExport()
'
' CSVExport Macro
' Exports selected items as CSV
'
' Keyboard Shortcut: Ctrl+Shift+C


   ' Dimension all variables.
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer

   ' Prompt user for destination file name.
   DestFile = InputBox("Enter the destination filename" _
      & Chr(10) & "(with complete path):", "Quote-Comma Exporter")

   ' Obtain next free file handle number.
   FileNum = FreeFile()

   ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To Selection.Rows.Count

      ' Loop for each column in selection.
      For ColumnCount = 1 To Selection.Columns.Count

         ' Write current cell's text to file with quotation marks.
         Print #FileNum, """" & Selection.Cells(RowCount, _
            ColumnCount).Text & """";

         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
         Else
            ' Otherwise, write a comma.
            Print #FileNum, ",";
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
End Sub

Open in new window

0
 

Author Comment

by:ka8799
ID: 38302125
SA,
Thank you for the quick reply. I am currently doing what you recommended.  The excel file field format is text but when I open the csv file being generated the same cell field format changes to Custom (mmm-yyy)
Thank you,
KA
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 38302223
If you open the csv file, is the value correct (5-2026) or incorrect (May-26) ?

Maybe you'll need to IMPORT the column to Excel AS TEXT instead of using the predefined "Standard" formatting. That way the column values go unchanged and formatted as text into your worksheet.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:ka8799
ID: 38302301
It is showing incorrectly (May-26)

Here is some of the code:
DataArray = ThisWorkbook.Sheets("Table").Range("A2").CurrentRegion.Value
FieldList = ThisWorkbook.Sheets("Test").Range("FieldList").CurrentRegion.Value
 
 
For r = 2 To UBound(DataArray)
    For c = 2 To 18
        If r > 1 And FieldList(c, 4) = "Date" Then
            OutputText = OutputText & Format(DataArray(r, FieldList(c, 3)), "mm/dd/yyyy") & ","
        Else 'don't enclose
            OutputText = OutputText & DataArray(r, FieldList(c, 3)) & ","
                       
        End If
    Next c
    Print #1, OutputText
    Debug.Print OutputText
    OutputText = ""
Next r
0
 
LVL 24

Expert Comment

by:Steve
ID: 38302306
In place of putting the data inside quotation marks, have you tried a single apostrophe at the start...

Print #FileNum, "'" & Selection.Cells(RowCount, ColumnCount).Text;

Open in new window


this forces excel to read it as text not date.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38302909
untested suggestion:

instead of using .value use .text.

DataArray = ThisWorkbook.Sheets("Table").Range("A2").CurrentRegion.text
FieldList = ThisWorkbook.Sheets("Test").Range("FieldList").CurrentRegion.text


-SA
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38303046
How are you opening the file?

If you open it in Notepad, it should come up as 5-4026.

If you open it in Excel, Excel will try to be "helpful" by turning anything that looks like a date into a date.

If you must open this from Excel, use the text file import wizard, which will allow you to specify the data type for each column.
0
 
LVL 14

Accepted Solution

by:
frankhelk earned 1500 total points
ID: 38303815
If you open it in Notepad, it should come up as 5-4026.

Ooops - that's what I've meant when asking to "open it".

If you must open this from Excel, use the text file import wizard, which will allow you to specify the data type for each column.

Addendum: To actually get into the wizard, you'll have to rename the file to .txt ! Otherwise Excel tries to be helpful, too, and automagically assumes .csv (as it likes it, even when it just perverts the format ... CSV comes from "Comma Separated Values", and Excel standard is to separate values with semicolons (;)). Have I said that I hate programs who try to outsmart me by making silly assumptions ?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38304858
frankhelk wrote:

Addendum: To actually get into the wizard, you'll have to rename the file to .txt !

No, you don't :)

In Excel 2007/2010, go to the Data tab on the Ribbon, and click "From Text" in the "Get External Data" group.  That will launch the text import wizard.  Be sure to set the delimiter as comma, and to set the column with the 5-4026 value as Text and not Date or General.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 38890468
frankhelk wrote:
Addendum: To actually get into the wizard, you'll have to rename the file to .txt !

No, you don't :)

In Excel 2007/2010, (...)

OK - I've answered out of my decade of Excel 2003 experience ... where my statement is completely true. I don't have Office 2010 for such a long time by now ...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question