Mac – How to force Excel to treat dates in CSV as text in a macro

csvmacrosmicrosoft excel

I have a macro that lets the user select a CSV file and then manipulates it. However, it treats cells that contain things like 1/2/12 as a date. I need to keep these strings as text, not dates.

I know I can do that by
1) start a new worksheet
2) import the CSV instead of opening it. Set the column with the dates to "text" and finish.

The question is: how can I interrupt Data > Import so that the user can select the file. After that, the macro should continue to set the format of the columns, finish the import and perform the data manipulations.

Edit:
Here is the relevant code of the macro as it stands:

ChDir "C:\RoomTimerData\"

MyFile = Application.GetOpenFilename("Comma Separated Values
(.csv),.csv")

Workbooks.Open fileName:=MyFile

Best Answer

Usually one would select the data-type for each imported column as step 3 of 3 of the text-import dialog. To avoid misformatting, just use text as format, and you're good.

However, you seem to use a VBA macro to import your CSV files. So I just recorded such an uncorrupted text import:

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\test.csv", Destination:=Range("$A$1"))
    .Name = "test_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 1) 'THIS IS THE MAGIC LINE!
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Also, look at this example from excel-help for TextFileColumnDataTypes:

Set shFirstQtr = Workbooks(1).Worksheets(1) 
Set qtQtrResults = shFirstQtr.QueryTables _
        .Add(Connection := "TEXT;C:\My Documents\19980331.txt", _
        Destination := shFirstQtr.Cells(1, 1))
With qtQtrResults
    .TextFileParseType = xlFixedWidth
    .TextFileFixedColumnWidths = Array(5, 4)
    .TextFileColumnDataTypes = _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
    .Refresh
End With

These are the formats you can use:

xlGeneralFormat
xlTextFormat
xlSkipColumn
xlDMYFormat
xlDYMFormat
xlEMDFormat
xlMDYFormat
xlMYDFormat
xlYDMFormat
xlYMDFormat

Related Solutions

Windows – How to convert an ordinary text file to a .csv file, and import it to Excel

What you need isn't Excel. Excel and Calc (OO.o) can both open CSV as spreadsheets, but what you want is something to manipulate the text file into a properly-formatted CSV file.

For this you want a good text editor. There are very few text editors that allow you to do multiline search and replace (don't know why, as this is an incredibly useful feature and easy to implement). But a very good free editor that (sorta) has this functionality is Notepad2. It is basically an upgraded version of Windows' default Notepad, but not quite as bulky as Notepad++. You can replace Windows Notepad with Notepad2 or Notepad++, but that's beyond the scope of this answer.

After installing Notepad2:

Open up the text file and hit Ctrl + H to bring up the find & replace dialog.
Type a comma or comma and space into the Search String box.
Now you'll notice that the Replace with: box is only a single-line. That's OK because Notepad2 supports regex right out of the box. So check the box that says Regular expression search.
Despite what the above checkbox reads, it's actually Regular expression search AND replace—meaning that you can use regexps in the Replace box as well. So in it, type \n and hit Replace All.
Then just save as *.csv and quit. If you open it up in Excel/Calc, it should now be a column of names/e-mails.

Edit:

Actually, re-reading your question, it occurs to me that you may want 2 columns: 1 for email, one for name. If that's the case, then it's a little more difficult to do than just replacing all commas with line-breaks. But fear not! Regexps are here to help once more:

Open up the original text file.
Pull up the search & replace dialog.
Type this into the search box: \([^,]+\),\([^,]+\),
Type this into the replace box: \1,\2\n
Hit Replace All.
Save as *.csv and quit.

You may optionally modify the regexps to put a space after the commas if that is how the text file is formatted or how you want it formatted, e.g.

first last, user@domain.com, bob builder, bob@builder.com, foo...
versus:
first last,user@domain.com,bob builder,bob@builder.com,foo...

Edit2:

For the Notepad++ flavor of regexp, you don't need to escape groupings with backslashes, so the corresponding search regexp would be:

([^,]+),([^,]+),

If there are empty email addresses:

([^,]+),([^,]*),     // assuming that the email is the second parameter
([^,]*),([^,]+),     // make the first parameter optional

Edit3:

To deal with completely missing fields, it gets a little messy. This is because neither Notepad2 nor Notepad++ support optional groups or negative lookbehinds. So we need to apply 2 different regexps:

This takes all consecutive e-mails and injects an empty field (, ,) between them. Run this (with Replace All) multiple times until you stop getting matches.:
```
([^,]+@[^,]+), ([^,]+@[^,]+),*   // search for
\1, , \2,                        // replace
```
Manually add the extra empty field at beginning or end of document if needed.
Depending on whether the e-mail comes first or name comes first, replace either of these:
```
([^,]+), ([^,]*),\s     // email first
([^,]*), ([^,]+),\s     // name first
```
with:
```
\1, \2\n
```

And that should do it. The \s above is just a space character. You can either put a space there or write \s in the regexp.

Note: I normally try to avoid asking a person to install a new text/graphics/audio/video editor just to solve a specific problem, but this is one instance where: 1.) it's a very lightweight (~300KB) free program that I know works; 2.) it's incredibly useful and almost anyone who hasn't already installed a notepad replacement like Notepad2/Notepad++ would benefit from installing it. In fact, I install it on every Windows computer I use, and also every Windows workstation at my work.

Excel – How to change default delimiter in the Text Import in Excel

This is a Windows setting that will be used by all programs that refer to it (Excel in this case).

Click the Start button, and then click Control Panel.
Open the Regional and Language Options dialog box.
Do one of the following: In Windows Vista/7, click the Formats tab, and then click Customize this format. In Windows XP, click the Regional Options tab, and then click Customize.
Type a new separator in the List separator box.
Click OK twice.

Best Answer

Related Solutions

Windows – How to convert an ordinary text file to a .csv file, and import it to Excel

Excel – How to change default delimiter in the Text Import in Excel

Related Question