VBA - text import puts contents all in one cell, need comma delimit instead

Sayre · Aug 28, 2014

Hi, I found and modified the below code and corresponding custom function hoping it will work faster than Workbooks.OpenText on large csv file imports (500K rows+ in some cases). It works great but writes all the data to just one cell in the worksheet which obviously won't work for large files.

How do I modify it so it writes it to the sheet more like a proper comma delimited across man rows/columns? I found threads on split function but not sure if that's the way to go or how to incorporate it if it is. Hoping there's a few simple lines I can add to the below script or custom function. If its more complicated than that could anyone at least point me towards a potential solution? Any hep truly appreciated....

Here's the script...

Code:

Sub TxtImporter2()
Dim f As String, flPath As String
Dim i As Long, j As Long
    Dim sPath As String
Dim ws As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
flPath = Sheets("START").Cells(9, 1).Value & Application.PathSeparator
i = ThisWorkbook.Worksheets.Count
j = Application.Workbooks.Count
f = Dir(flPath & "*.csv")
Do Until f = ""
Workbooks.Add
 Cells(1, 1) = LoadTextFile2(flPath & f)
    Workbooks(j + 1).Worksheets(1).Copy After:=ThisWorkbook.Worksheets(i)
    ThisWorkbook.Worksheets(i + 1).Name = Left(f, Len(f) - 4)
    Workbooks(j + 1).Close SaveChanges:=False
    i = i + 1
    f = Dir
Loop
Application.DisplayAlerts = True
End Sub

Here's the corresponding function...

Code:

 ' \\ Function to return the full content of a text file as a string
Public Function LoadTextFile2(sFile As String) As String
    Dim iFile As Integer
     
Dim lineSplit As Variant

    ' \\ Use FreeFile to supply a file number that is not already in use
    iFile = FreeFile
     ' \\ ' Open file for input.
    Open sFile For Input As #iFile


     
     ' \\ Return (Read) the whole content of the file to the function
    LoadTextFile2 = Input$(LOF(iFile), iFile)
    'Split(Symbols, ".")
     
    Close #iFile
    
End Function

DocAElstein · Sep 3, 2014

Rick Rothstein said:
…… beginning to wonder if I got anything right on that code!

……...

…… for my purposes, “learning by doing, testing out etc. ”- it is “right” … and very grateful again for the reply.

Alan.

DocAElstein · Sep 3, 2014

Hi Sayre,

Sayre said:
…………………………..

In any case I ended up going with a very simplified version that works well enough for my needs:

Code:

Sub TxtImporter() …… ………………………. …………………. f = Dir(flPath & "*.txt") 'looking for csv files only in specified path 'loop through all csv files in the specified folder path: Do Until f = "" 'open each csv in excel, append to the workbook that contains this macro Workbooks.OpenText flPath & f, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _ Space:=False, Other:=False, TrailingMinusNumbers:=True ……………………. f = Dir() [COLOR=#008000]'Dir without Parameter has the effect of going/ setting to the next File with the original search ……[/COLOR] ………………………… Loop ………………………….. MsgBox "import complete" End Sub

This works ok except when the .csv is not actually comma separated………QUOTE]

. Nice simple little Code. I went through it, modified it a bit and „stole it“ to add to my collection. Thanks!
. I guess you got the Open Text bit from a macro?
. ( Only bit that took me a while to get was the Dir or rather Dir() - The VBA Dir Method (Actually Dir is a very old File System thing that an old Dinosaur like me typed more often than I like to remember) without Parameter has the effect of going/ setting to the next File with the original search criteria……)

Alan.

DocAElstein · Sep 3, 2014

DocAElstein said:
.......
. I guess you got the Open Text bit from a macro?........

I meant macro recorder/ recording

Rick Rothstein · Sep 3, 2014

DocAElstein said:
Hi Sayre,

Sayre said:

…………………………..

In any case I ended up going with a very simplified version that works well enough for my needs:

Code:

Sub TxtImporter() …………………. f = Dir(flPath & "*.txt") 'looking for csv files only in specified path 'loop through all csv files in the specified folder path: Do Until f = "" 'open each csv in excel, append to the workbook that contains this macro Workbooks.OpenText flPath & f, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, [COLOR=#FF0000][B]Tab:=True[/B][/COLOR], Semicolon:=False, [COLOR=#FF0000][B]Comma:=True[/B][/COLOR], _ Space:=False, Other:=False, TrailingMinusNumbers:=True[COLOR=#FF0000][/COLOR] ……………………. f = Dir() [COLOR=#008000]'Dir without Parameter has the effect of going/ setting to the next File with the original search ……[/COLOR] ………………………… Loop ………………………….. MsgBox "import complete" End Sub

This works ok except when the .csv is not actually comma separated………QUOTE]

. Nice simple little Code. I went through it, modified it a bit and „stole it“ to add to my collection. Thanks!

Click to expand...

I see one possible problem with the settings I highlighted in red... I would think it possible that the tab delimited file was generated instead of a comma delimited one because the generating software "saw" one or more commas not being used as delmiters... marking both parameters True would mean new columns would be created for both the tabs delimiting the columns of data plus any commas contained in the text meant for inclusion in a single cell.

DocAElstein · Sep 3, 2014

Rick Rothstein said:
DocAElstein said:

Hi Sayre,.............

. Nice simple little Code. I went through i.......
Alan.

Click to expand...

,

I see one possible problem with the settings I highlighted in red... I would think it possible that the tab delimited file was generated instead of a comma delimited one because the generating software "saw" one or more commas not being used as delmiters... marking both parameters True would mean new columns would be created for both the tabs delimiting the columns of data plus any commas contained in the text meant for inclusion in a single cell.

Hi Rick,
. Ok, worth noting.
. ( The OP file’s he gave us are either comer or Tab delimitated files. So in that case the problem does not arise. I note that both files work with his program, contrarily to wot he said? -

Sayre said:
…………………………..

This works ok except when the .csv is not actually comma separated………

)

… By the way, I must always save as .txt rather than .csv I am not sure if that has any relevance? (If I try to save anything on my computer in .csv it saves it always as an Microsoft Office Excel-CSV file which looks when opening with Excel as a normal Excel File but with all data in the first column? If I open that file with the text editor and resave I only have the option .txt )

…Both my and your latest Codes work for both the OP’s data (I have to select .txt rather than .csv with yours for the reasons mentioned above.)

..For my code I have to modify it slightly in the case of the Tab delimitated file , that is to replace

Code:

Let Sp = Application.InputBox("Enter a separator character.", Type:=2) 'prompts user for separator character

With

Code:

Let Sp = vbTab

Because I do not know how to type in a tab in the InputBox

Alan Elston
Late in Bavaria

P,s.

Rick Rothstein said:
......

I am not sure they are really incorrect offsets... if the file contains a double tab character, I would assume whatever program was used to create the file did so on purpose.

. Yeah I may have been wrong about that. On second glance maybe it comes out as it should. I am just not sure exactly wot the OP wanted. (He has probably gone off this thread now, being overwhelmed with code possibilities. Importing data methods are almost as endless as the VBA code possibilities. – I just knocked up a code by trial and error that works using “Microsoft's ActiveX Data Objects database” connection stuff. It works (at least initially only with comer separated.)) I have no idea how / why it works!… I think I will leave that for my homework in a few evenings time. I think I’ll then not post it. – There is enough here

DocAElstein · Sep 4, 2014

Hi apo

DocAElstein said:
.. Super Code. ……
(My Homework tonight: Figure out how it works!)
Alan.

apo said:
……Step through .. (F8) .. Locals WIndow open.. that will help you understand how, first the s array and then the y array are being populated..

. With F8, Locals Window, along with setting watches in Watches Window, it was nice to go through and see and understand exactly wot was happening. ( I had to make a detour first and read a bit about Microsoft Scripting stuff!) .
. Now I have another method to bring in a text file into Excel.

Thanks again for posting the code!
. Along the way I noticed a few minor errors. The counts and sizes were not quite right and so the last 2 rows were missed. I think that is a typical occurrence because of the slightly different convention with Array indices and Excel cell indices, ( the 0, +1, stuff). Here is the corrected version of your code with corrections shown in Red.

Code:

 Sub Import()
    Dim i As Long, ii As Long, x, xx, s, strFile As String, y
    strFile = Application.GetOpenFilename("CSV Files,*.csv")
    If strFile = "False" Then Exit Sub
    s = Split(CreateObject("scripting.filesystemobject").getfile(strFile).openastextstream.readall, vbCrLf)
    ReDim y(1 To UBound(s) [COLOR=#ff0000]+ 1[/COLOR], 1 To 1)[COLOR=#008000] '####[/COLOR] [COLOR=#ff0000]+1[/COLOR]
    For i = 1 To UBound(s) [COLOR=#ff0000]+ 1[/COLOR][COLOR=#008000] '####[/COLOR] [COLOR=#ff0000]+1 instead of -1[/COLOR]
        xx = Split(s(i - 1), ",")
        ReDim Preserve y(1 To UBound(s) [COLOR=#ff0000]+ 1[/COLOR], 1 To UBound(xx)) [COLOR=#008000]'#### [/COLOR][COLOR=#ff0000]+1[/COLOR]
        For ii = 1 To UBound(xx)
            y(i, ii) = xx(ii - 1)
        Next ii
    Next i
    Sheets("Sheet1").Cells(1, 1).Resize(UBound(y, 1), UBound(y, 2)).Value = y
    With Sheets("Sheet1").UsedRange
        x = .Replace("""", "", xlPart)
    End With
End Sub

. There was only one bit I really struggled to understand. That was the last

Code:

With Sheets("Sheet1").UsedRange
        x = .Replace("""", "", xlPart)
    End With

bit. I expect it has something to do with checking / correcting for any unwanted double quotes. But exactly how, why , if, what it was doing I could not quite understand . if you have time could you explain it to me? (The code appears to work fine without that last bit)

Many Thanks
Alan Elston

P.s. 1 . Any idea how it could be modified for Tab (vbTab) delimited? (A simple substitution of “,” with vbTab does not work. )

P.s.2. Just for anyone mad enough too want to see my expanded code version with ‘green comment graffiti… here it is:

' Sub ImportapoAlan() ' using the Object FileSystemObject from Microsoft's Scripting library. ' recommended (early binding) initially in Tools>>References>>then check Microsoft Scripting Runtime Library ' So we have Scripting (Runtime) library available ' u.a. in there is the class FileSystemObject (FSO) . That allow access to the host computer's file system, or rather "sort of being able to use the old DOS like comannds" ' Typically we start start by creating an instance of the FileSystemObject, ours we want for text reading (but we get all the properties and methods by creating an instance of that claArraySplitStreamString. Syntax fo that is: Dim objFilepathSOtxtRead As Object Set objFilepathSOtxtRead = CreateObject("Scripting.FileSystemObject") Dim objFilepath As Object ' the given file path is in fact an object! Dim StreamString As String, ArraySplitStreamString 'The got stream comes out as a (very) long string, that split (ArraySplitStreamString) then become an Array Dim RowNumberinFinalArray As Byte, ColumnNumberinFinalArray As Byte 'Limit initially sizes to 255 Dim x ' No idea???? Dim TempArrayForRowSplitinColumn 'Becomes Array by Split Method Dim y() As String ' The final Array looking as we want in Excel. Dim strFile As String Let strFile = Application.GetOpenFilename("CSV Files,*.txt") 'Get Dialogue box for opening Files If strFile = "False" Then Exit Sub Dim FinalShtRange As Range 'Final range in Spreadsheet. '..from apo.. ArraySplitStreamString = Split(CreateObject("scripting.filesystemobject").getfile(strFile).openastextstream.readall, vbCrLf) '....expanded by me..!!!!:- Set objFilepath = objFilepathSOtxtRead.getfile(strFile) 'Looks like a sgring but is actually an object StreamString = objFilepath.openastextstream.readall ' returns long string for entire File ArraySplitStreamString = Split(StreamString, vbCrLf) 'Split long string into rows looking similar to oroginal text Files ReDim y(1 To UBound(ArraySplitStreamString) + 1, 1 To 1) ' (re)Set size to 1 column of row number equal to the line number. Must do this as further ReDim only allows 1 index to change For RowNumberinFinalArray = 1 To UBound(ArraySplitStreamString) + 1 'For each row.... ( .From 1 to Array index+1(+1 because Array starts at 0) TempArrayForRowSplitinColumn = Split(ArraySplitStreamString(RowNumberinFinalArray - 1), ",") ' -1 to get back to Array index convention ReDim Preserve y(1 To UBound(ArraySplitStreamString) + 1, 1 To UBound(TempArrayForRowSplitinColumn)) ' change column size to number of split columns For ColumnNumberinFinalArray = 1 To UBound(TempArrayForRowSplitinColumn) '....go througth each column y(RowNumberinFinalArray, ColumnNumberinFinalArray) = TempArrayForRowSplitinColumn(ColumnNumberinFinalArray - 1) 'Most importan doing line: Give final array the value in appropriate place. Next ColumnNumberinFinalArray ' going along each column until all done then.... Next RowNumberinFinalArray ' start again for the next Row Set FinalShtRange = Sheets("apoLCS").Cells(1, 1).Resize(UBound(y, 1), UBound(y, 2)) 'Set Final Range to size of final Array Let FinalShtRange.Value = y 'copy Array to final range (will only work if sizes are identical) With Sheets("apoLCS").UsedRange x = .Replace("""", "", xlPart) 'Not sure if , What , Why or how that is doung anything. Appears not to be necerssary End With End Sub 'ImportapoAlan()

Sayre · Sep 4, 2014

What a great thread this has turned out to be! I've been taking a peak at it here and but had no time to reply until now.

DocAElstein, you are right, your simplified code does work just fine. I think I was trying it on the wrong file which must have a different kind of line-end indicator so it spewed everything onto one line. The line separators got interpreted as spaces so I ended up with everything in row 1 with spaces at regular intervals. Once I tried it on another file everything lined up properly.

DocAElstein said:
I note that both files work with his program, contrarily to wot he said?

Yes, my code does technically work, sorry for the confusing language. Its just that if the csv file is actually delimited by spaces, then everything ends up in column A (except for bits of text that just happen to have commas in them).

Rick Rothstein said:
I see one possible problem with the settings I highlighted in red...

Good catch Rick! I did not mean to have both delimiters in there. I corrected it to say false for tab delimiter, thank you!

DocAElstein said:
. I guess you got the Open Text bit from a macro?........
I meant macro recorder/ recording

Right again. Sometimes it helps to start with the simplest ways of doing things. I recorded myself opening a csv file into excel, then modified it a bit and turned it into a loop that goes through all csv files in a specified folder. Glad you like it! Another variation on that is to use the query function. See below:

Code:

    Dim myarray() As Variant

    For K = 0 To 16384
        ReDim Preserve myarray(K)
        myarray(K) = 2 'designates that all columns imported in the query/import will be in text format
    Next K
    
    ActiveWorkbook.Sheets.Add After:=Sheets(i)
               
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & flPath & f, Destination:=Range("A1"))
        .Name = "mytest"
        .FieldNames = True
        .AdjustColumnWidth = True
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = myarray
        .Refresh BackgroundQuery:=False
    End With
        ActiveSheet.Name = f
    End If
    End If
    i = i + 1
    f = Dir
    'need to redefine the variables i & j after the loop passes over the files in the exclusions list. Otherwise the macro loses track of what these variable values are supposed to be in the loop:
i = ThisWorkbook.Worksheets.Count
j = Application.Workbooks.Count

Loop

I recorded myself using the Get External Data (from text) button on the Data ribbon to import the file this time and then googled up how to modify it so it always imports every column as text format. Importing certain values into excel from text files can inadvertently transform them. Pesky problem and this is a way to ensure that does not happen.

Apo, I used your code as well and it most definitely works fast on big files! Using the string function was exactly what I was talking about. DocAElstein is right, that is some super code. I think this will be my go to method for all large files.

My next challenge is to figure out a way to have the code detect whether a file is space delimited or comma delimited and then adjust the way it imports accordingly.

Or alternatively, parse only the first 3 or 4 delimiters in a row and the rest does not delimit? Example: A text file with 10-15 values per row separated by commas (or spaces), but only the first 3 values get separated, the rest all end up in the 4th column, commas and all.

Thanks a ton for all the input here! Just like DocAELstein I'm learning a lot and hope I'm contributing something of value as well.

Rick Rothstein · Sep 4, 2014

Sayre said:
My next challenge is to figure out a way to have the code detect whether a file is space delimited or comma delimited...

That file is not "space delimited"... it is "Tab" delimited. I posted complete code to load your files into Excel for you in Message #15, part of which has code to automatically determine if the file is tab delimited or comma delimited. The test is kind of tied to how I read the file in (all at once into a single String variable which I named TotalFile), but here it is for you to study (here I set a variable named Delim which is used to store the delimiter for the file for use in later code lines)...

Code:

  If InStr(TotalFile, vbTab) Then
    Delim = vbTab
  Else
    Delim = ","
  End If

Sayre · Sep 5, 2014

!!! Well that's what I get for not keeping up with the thread. Rick you're way ahead of me thanks so much! I'll take a look as soon as I can tomorrow.

DocAElstein · Sep 5, 2014

Hi Sayre,

Sayre said:
!!! What a great thread this has turned out to be! I've been taking a peak at it here and but had no time to reply until now…..Well that's what I get for not keeping up ……. take a look as soon as I can tomorrow.

…I know the feeling. When You are laerning like me You’d like to keep on it but other things pull you away. It is annoying that when we are not able to spend more time on it. That is why it is so good that there are Forums like this with people like Rick giving so much of their time voluntarily..

Sayre said:
!!! …... Rick you're way ahead of me thanks so much! I'll …...

…Rick…..We are not worthy!

ray:

…Sayre…………………..
. But it is much appreciated that You give feedback when You can and contribute back. - I’ll take a look at your latest stuff when I get time!!

. I had a go at something new. It is not complete and I am not quite happy with it just jet. – There are a few problems* and the whole thing I do not really understand. – I just Cobbled it together with a lot of googling and even more trial and error. It sort of works. But:
.* It only does csv files
.* when the format of any entry in some columns changes the entry can vanish!! (In the example below I changed 1068 with 1068h and.. it vanished!?!
.* It does not like ### in the heading. – It crashes! (So I changed that to XXX in the example below)
.* It seems to not recognize the decimal point? (That could be the problem with my German system which confuses the different English/German conventions with a , comma and point . for the decimal point?)
.* It does not copy the headers (But needs them**)

. Here is a screen shot highlighting the problems (**Note it does not copy the headings, but it has to have them and the Code must be given them)

Book1

A

B

C

D

E

F

G

H

1

2

USSJCIRCL001PRD

1065

__LINEID__

Disk_0

Online

2790

0

3

USSJCIRCL002PRD

1066

__LINEID__

Disk_0

Online

2790

0

4

USSJCIRCL003PRD

1067

__LINEID__

Disk_0

2Online

2790

0

5

USSJCIRCL004PRD

__LINEID__

Disk_0

Online

2790

0

6

USSJCIRDB001PRD

1056

__LINEID__

Disk_0

Online

1360

0

apoLCS

. I was not going to post the code especially as it does not work fully yet. But as you may be “back on” I thought I would include it out of interest. Here it is:-

Sub GetCSVtxtDataADOdb2() 'Using Microsoft's ActiveX Data Objects database to read text file to VBA ' This requires that you add a reference ' Microsoft Active Data Objects to support the ADO code. ' The idea is to create an ADO connection to the csv (txt) files folder, ' then select the columns you want using a standard SQL statement in ' an ADO Recordset to link to the actual file. ' So you Need to reference the Microsoft ActiveX Data Objects 2.5 Library (called early binding) ' Tools>>References>>then check Microsoft ActiveX Data Objects 2.5 Library ' ..Or crashes..... 'Dim DBcnn As ADODB.Connection ' ......here. 'Set DBcnn = New ADODB.Connection ' ' The next two lines are an alternative called Late binding. Dim DBcnn As Object Set DBcnn = CreateObject("ADODB.Connection") Dim TextdtaPathstr As String 'For a text file, Data Source is the folder, not the file Let TextdtaPathstr = ThisWorkbook.Path Let DBcnn.Provider = "Microsoft.Jet.OLEDB.4.0" Let DBcnn.ConnectionString = "Data Source=" & TextdtaPathstr & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited;""" DBcnn.Open ' So now you are "connected" or "pluged in" or "the tap is turned on!" and stuff can be got! 'Set up Excel File for recieving data Dim nextRow As Integer Let nextRow = 2 ActiveSheet.Cells.Clear 'This bit gets wot can be now got, for example a record set. You need to have and Know what your headings are. Dim DBRecordset As ADODB.Recordset Set DBRecordset = New ADODB.Recordset Dim TextdtaFilename As String Let TextdtaFilename = "SayreTextDataCSV_XXX" DBRecordset.Open "SELECT hostname,hostuid,lineid,Disk_XXX,Status,Size_GB,Free_GB,Dyn,Gpt FROM [" & TextdtaFilename & ".txt]", DBcnn ' Note: You can change hostname,hostuid,lineid,Disk_XXX,Status,Size_GB,Free_GB,Dyn,Gpt with * DBRecordset.MoveFirst ' Moveto next "Record". For the case of a text file it is the next Row / line. But it is more compliceted with ACCESS etc.! ' This bit copies the selected records, ' starting at the row below the last used row in the Sheet. ActiveSheet.Cells(nextRow, 1).CopyFromRecordset DBRecordset 'And then finally close and dispose of the connection and recordset. -Good practice to close / shut off all these things! DBRecordset.Close DBcnn.Close Set DBRecordset = Nothing Set DBcnn = Nothing End Sub 'GetCSVtxtDataADOdb2()

. If you happen to test it out I would be interested, for example if you get the same problem with the decimal point not being recognized on your English system.
. Here is the slightly modified text data File of yours that I am using. (Put it in the same Folder as the Excel File you copy the code in.
FileSnack | Easy file sharing

Note the comments about the Early / late Binding stuff. But I think I have organized it ( Late Binding) that it should work straight away).
. A guy on you tube uses this method a lot very successfully but with ACCESS files rather than Text Files.. I may try to contact him to see if he could help

………………………

Sayre said:
!!! Apo, I used your code as well and it most definitely works fast on big files! Using the string function was exactly what I was talking about. DocAElstein is right, that is some super code....
.

. I agree – my favorite so far. It is something a little bit along the lines of my latest code..--- But his works!!, that is to say it does not seem to have the problems that mine does, (Shame it does not do Tab delimiters)....(But

Sayre said:
!!! Apo, I used your code ....... I think this will be my go to method for all large files.
.

….. Do not forget the minor correction I made to his code if you use it or you will lose the last two rows of your data!!

Alan

VBA - text import puts contents all in one cell, need comma delimit instead

Sayre

Board Regular

DocAElstein

Banned user

Excel Facts

DocAElstein

Banned user

DocAElstein

Banned user

Rick Rothstein

MrExcel MVP

DocAElstein

Banned user

DocAElstein

Banned user

Sayre

Board Regular

Rick Rothstein

MrExcel MVP

Sayre

Board Regular

DocAElstein

Banned user

Similar threads

Forum statistics

Share this page

VBA - text import puts contents all in one cell, need comma delimit instead

Board Regular

Banned user

Excel Facts

Banned user

Banned user

MrExcel MVP

Banned user

Banned user

Board Regular

MrExcel MVP

Board Regular

Banned user

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock