Import CSV without formatting

Question

I am trying to import a CSV file into my current workbook; however, it is auto-formatting some of my data. Is there a way to prevent that?

Example:

01JAN-1

Auto-converting to:

1-Jan-01

I looked up adding Local:= true but I'm not sure how to implement that. I've tried wb = Workbooks.Open(fileImportName, Local:=True)

Code:

Sub file_Import()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim fileImportPath As String, fileImportName As String, total As Integer, currentWorkbook As String

    fileImportPath = "C:\Users\UserName\Desktop\Test Data.csv"
    fileImportName = "Test Data.csv"
    currentWorkbook = "Testing Import.xlsm"
    total = Workbooks(currentWorkbook).Worksheets.Count

    Workbooks.Open (fileImportName)

    Workbooks(fileImportName).Worksheets(Sheets(1).Name).Copy _
    After:=Workbooks(currentWorkbook).Worksheets(total)

    Workbooks(fileImportName).Close

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

If you manually change the column back to `Text` after import, does it display correctly? If so, this may answer your question: https://stackoverflow.com/questions/8265350/how-to-change-format-of-a-cell-to-text-using-vba — Mako212, Dec 29 '17 at 19:23
It does not, it converts it to 36892 when I try to convert it to Text. I want it in its original form since this is not a date, it is an item name. — J. D., Dec 29 '17 at 19:27
You have to Format the cells to Text NumberFormat = "@" before Paste — 0m3r, Dec 29 '17 at 19:48
Is there any auto-conversion happening that you actually need/want? You could read the CSV contents into memory, assign to 2-dimensional array, then write to sheet. But you would totally lose out on any wanted/helpful auto-conversion with this approach. — chillin, Dec 29 '17 at 20:01
@0m3r The NumberFormat = "@" does not work since when my code opens up the csv in excel, it is already in the date format. When I open the csv file in textpad, it is in normal text. So copying the csv in excel over will just bring over the auto-conversion. — J. D., Dec 29 '17 at 20:05
@chillin I do not need any auto-conversion, just raw data. If you can advise me on how to do what you're proposing, that would be great. — J. D., Dec 29 '17 at 20:06
Sorry ran out of time, You need to add a new worksheet to the destination file, in the new worksheet you must format the column (where your "item name" will be pasted) as text, then open your .cvs file and copy the used range and paste to Range "A1" in the new worksheet. — GMalc, Dec 29 '17 at 20:47
You have to create the sheet before copying- create sheet - > format columns or sheet then copy and paste- — 0m3r, Dec 29 '17 at 20:48
Here is Copy paste examples https://stackoverflow.com/a/34886033/4539709 — 0m3r, Dec 29 '17 at 20:51
Copy paste does not work; even if you format destination sheet before pasting. The reason is because when you open the csv, it autoformats my data into a date. The format is already in date format when csv is open in excel so you're just copying a date to the new cell. When opened in excel, it looks like this already: "1-Jan-01". When open in text file, it looks like this: "01JAN-1". — J. D., Jan 02 '18 at 13:54
Try saving "01JAN-1" in a textpad to csv. Then open the csv file in excel, it will open as "1-Jan-01" no matter what. Copy and pasting will always look like "1-Jan-01". — J. D., Jan 02 '18 at 13:55

score 2 · Accepted Answer · answered Dec 29 '17 at 21:15

Code below tries to overwrite contents of first worksheet. Save copy of workbook before running to be safe. You will need to provide full path to CSV. I assume you have the same number of columns on each line of your CSV:

Option explicit

Sub CSVtoSheet()

Const FILEPATH as string = "C:\New Folder\test.csv" 'Replace with your own path; or consider changing to variable and assign dynamically.'

Const DELIMITER as string = ","

' Read into memory; assumes file will fit and is not too big.'
Dim FileContents as string
Open FILEPATH for binary access read as #1
Filecontents = space$(lof(1))
Get #1, 1, filecontents
Close #1

' Assign lines in file to 1-dimensional, 0-based array of strings'
Dim AllLines() as string
AllLines = split(filecontents,vbNewLine)

Dim NumberOfRows as long
Dim NumberOfColumns as long

NumberOfRows = ubound(alllines)+1'Watch out if last line of CSV is blank, as is sometimes the case. Rows which do not contain column delimiter can produce error/unwanted behaviour.'

' Assume number or columns is fixed throughout CSV and can be reliably deduced from first line alone'
NumberOfColumns = ubound(split(alllines(lbound(alllines)),delimiter))+1

Dim ArrayToWriteToSheet() as string 'Change to as variant if you need numeric values as numbers'
Redim ArrayToWriteToSheet(1 to NumberOfRows, 1 to NumberOfColumns)

' Iterate through each element in array'
Dim RowIndex as long, ColumnIndex as long
Dim TemporaryArray() as string

For RowIndex = lbound(arraytowritetosheet,1) to ubound(arraytowritetosheet,1)

If Len(alllines(rowindex-1)) > 0 then ' Skip any blank lines; sometimes final line in CSV is empty which can result in errors/unwanted behaviour.'

TemporaryArray = split(alllines(rowindex-1),delimiter)

For ColumnIndex = lbound(arraytowritetosheet,2) to ubound(arraytowritetosheet,2)

Arraytowritetosheet(RowIndex,ColumnIndex) = temporaryarray(columnindex-1)

Next columnindex

End if

Next rowindex

'Write to first sheet in workbook. Hopefully, Excel will not do any unwanted auto-conversion.'
Thisworkbook.worksheets(1).range("A1"). Resize(ubound(arraytowritetosheet,1),ubound(arraytowritetosheet,2)).value2 = arraytowritetosheet

End sub

Does it do what you want? In theory, once you have the values in memory, you can format/present however you want before writing back to sheet.

Untested, written on mobile.

I will work with this since simple copy and paste does not work. Will let you know and vote once I get something working. Thank you! — J. D., Jan 02 '18 at 13:50
This way works, I just need to understand the code and modify it so it grabs all columns. Thank you! — J. D., Jan 02 '18 at 16:52
If you want any specific parts of the code commented in greater detail, let me know. — chillin, Jan 02 '18 at 18:39
@chillin thank you so much for your solution. amazing you could type that out so accurately without syntax check. Two questions: 1) there is no alternative to preserving TEXT values from a csv? 2) Is there a simple way to remove or prevent `"` from appearing in ArrayToWriteToSheet() where the value is an actual string? Right now I am looping thru the columns I need to REPLACE() them with `""`. Thanks — rockhammer, Jul 07 '20 at 18:59
@rockhammer, sorry for the late response, I don't use StackOverflow everyday. #1 One approach might be to get the range (that the values are being written to) and apply text number formatting before the write. So something like `someRange.NumberFormat = "@"`, followed by `someRange.Value2 = ArrayToWriteToSheet` -- and that should keep everything as it was. (Alternatively, you could apply text number formatting to only the columns you need to preserve as text.) If you need an example, let me know. — chillin, Jul 10 '20 at 19:16
#2 No, unfortunately there is no simple way in this answer to remove or prevent `"` from appearing in the output (other than looping through the array and stripping `"` from the start and end of the array values). A major shortcoming of my answer is that it doesn't handle escape quotes (which is why you see `"` in the values). — chillin, Jul 10 '20 at 19:55
Better approaches (than this answer) might be to try: text-to-columns with VBA -- or Power Query with VBA (since Power Query can parse CSVs via `Csv.Document`) -- or replace the `Split` function used in this answer with a custom function which handles escape characters correctly. In other words, get some existing feature to parse the CSV rather than re-inventing the wheel. — chillin, Jul 10 '20 at 19:55
@chillin thanks a lot for your detailed answers! Will explore them — rockhammer, Jul 10 '20 at 20:10

Import CSV without formatting

1 Answers1