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

Sayre · Sep 6, 2014

DocAElstein,

I ran your code and got similar results but not exactly the same:

Rich (BB code):

USSJCIRCL001PRD	1065	__LINEID__	Disk_0	Online	279	0
USSJCIRCL002PRD	1066	__LINEID__	Disk_0	Online	279	0
USSJCIRCL003PRD	1067	__LINEID__	Disk_0	2Online	279	0
USSJCIRCL004PRD		__LINEID__	Disk_0	Online	279	0
USSJCIRDB001PRD	1056	__LINEID__	Disk_0	Online	136	0
USSJCIRDB001PRD	1056	__LINEID__	Disk_1	Online	186	0
USSJCIRDB001PRD	1056	__LINEID__	Disk_2	Online	1000	0
USSJCIRDB001PRD	1056	__LINEID__	Disk_3	Online	1000	0
USSJCIRDB001PRD	1056	__LINEID__	Disk_4	Online	1000	0
USSJCIRDB001PRD	1056	__LINEID__	Disk_5	Online	1250	0
USSJCIRDB001PRD	1056	__LINEID__	Disk_6	Online	1250	0

DocAElstein said:
.* It seems to not recognize the decimal point?

My result is different so you may very well be right about it being a german setting. For example the number in the first row shows up as "279" in mine. In yours it shows up as "2790". Seems reasonable to conclude that your settings are interpreting the "." the way american number system interprets ",".

DocAElstein said:
.* It does not copy the headers (But needs them**)

The header row was omitted in mine just like yours. I googled this problem and found stuff on changing extended properties "HDR=Yes" to "HDR=No". I tried this but got an error "No Value Given For One Or More Parameters".

I fixed that by using your suggestion of using "*" instead of the actual header names. So the headers import with the rest of the data now, but misaligned, not in the porper column.
Here's the new results:

Rich (BB code):

hostname		lineid	Disk_XXX	Status			Dyn	Gpt
USSJCIRCL001PRD	1065	__LINEID__	Disk_0	Online	279	0		
USSJCIRCL002PRD	1066	__LINEID__	Disk_0	Online	279	0		
USSJCIRCL003PRD	1067	__LINEID__	Disk_0	2Online	279	0		
USSJCIRCL004PRD		__LINEID__	Disk_0	Online	279	0		
USSJCIRDB001PRD	1056	__LINEID__	Disk_0	Online	136	0		
USSJCIRDB001PRD	1056	__LINEID__	Disk_1	Online	186	0		
USSJCIRDB001PRD	1056	__LINEID__	Disk_2	Online	1000	0		
USSJCIRDB001PRD	1056	__LINEID__	Disk_3	Online	1000	0

That's all I have for now, sorry its not more helpful.

Here's the complete code with my revisions if it helps at all:

Rich (BB code):

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=No;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 * FROM [" & TextdtaFilename & ".txt]", DBcnn ' Note: You can change   hostname,hostuid,lineid,Disk_XXX,Status,Size_GB,Free_GB,Dyn,Gpt   with   *
'DBRecordset.Open , "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()

Sayre · Sep 6, 2014

Sayre said:
the headers import with the rest of the data now, but misaligned, not in the proper column.

Now that I review this again, that statement is not correct, the headers are properly aligned, but 3 of the headers are missing:hostuid, Size_GB, Free_GB

I don't understand why exactly but on the little reading I did on this it seems the ADO import method is sensitive to text values vs. numeric values. if its expecting a numeric value and receives text, it simply omits it, and vice versa. Something like this appears to be happening here.

DocAElstein · Sep 6, 2014

Sayre said:
Now that I review this again, that statement is not correct, the headers are properly aligned, but 3 of the headers are missing:hostuid, Size_GB, Free_GB

I don't understand why exactly but on the little reading I did on this it seems the ADO import method is sensitive to text values vs. numeric values. if its expecting a numeric value and receives text, it simply omits it, and vice versa. Something like this appears to be happening here.

. Thanks for all that. I will take a good look again later. At a first glance looks like you have similar results to me... While I was developing that code, I remember along the way sometimes seeing headers but with some missing. So For now I went with the version that left them out.

. Thanks for the point with the coma. That was very helpful as I have no access to an English version here.

. The chap from YouTube suggested I should be using ACCESS rather than Text Files. He may be right, but I still have a tendance (at my age!) to stick with a simple text file for what I am doing. Buying and learning ACCESS I am trying to avoid. Especially as some experienced users advised me that ACCESS may not handle really big files anyway and I'd have to go on to learning SQL or whatever... then it would be never ending learning and I would never get my project finished in my life time!!...!

Thanks again for the Reply
Alan

Rick Rothstein · Sep 6, 2014

Just a follow-up, when I posted this in Message #28...

Rick Rothstein said:
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.

I had forgotten about the minor problem that Alan pointed out with one of my statements in the code posted in Message #15 which will cause you a problem if you try and run the code as written. Alan posted the corrected code in one of his follow up messages, but so you don't have to try and find it on your own, here is my code from Message#15 with the correction Alan made in it, so you would use this following code to process either of your file types (comma delimited or tab delimited)...

Code:

Sub ImportFile()
  Dim R As Long, C As Long, FileNum As Long
  Dim TotalFile As String, PathAndFileName As Variant[COLOR=#000000][/COLOR], Delim As String
  Dim Lines As Variant, Data As Variant, Fields As Variant
[COLOR=#000000]  If Application.CountA(Cells) Then[/COLOR]
    MsgBox "You have data on the active worksheet... this code can only be run " & _
           "when the active sheet is empty!", vbCritical, "Worksheet Not Empty"
    Exit Sub
  End If
  FileNum = FreeFile
  PathAndFileName = Application.GetOpenFilename("CSV Files(*.csv),*.csv,All Files (*.*),*.*", 1, "Open CSV File")
  Open PathAndFileName For Binary As #FileNum
[COLOR=#000000]    If PathAndFileName = False Then Exit Sub[/COLOR]
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Lines = Split(TotalFile, vbNewLine)
  ReDim Data(1 To UBound(Lines) + 1, 1 To 20)
  If InStr(TotalFile, vbTab) Then
    Delim = vbTab
  Else
    Delim = ","
  End If
  For R = 0 To UBound(Lines)
    Fields = Split(Lines(R), Delim)
    For C = 0 To UBound(Fields)
      Data(R + 1, C + 1) = Fields(C)
    Next
  Next
  Range("A1").Resize(UBound(Data), UBound(Data, 2)) = Data
End Sub

Sayre · Sep 6, 2014

Thanks a lot Rick! I was just getting ready to post again about this code. I've been playing with and noticed a couple things:

1) When I run this on my actual files I had to change this line of code:

Rich (BB code):

Lines = Split(TotalFile, vbNewLine)

to this:

Rich (BB code):

Lines = Split(TotalFile, vblf)

I was getting subscript out of range error in the VbNewLine version.

Now I fear I may have another variable to consider: Different text files could have different line-ending indicators! VbNewline, VbCrLf, VbLf... Is there a way to test for this as well as the delimiter type?

2) Also, the actual files I'm using are not tab delimited. They seem to have transformed into tabs on the portion of the file I copied into this thread for illustration purposes. So I really have a combo of space and comma delimited files (maybe at some point some will also be tab delimited, who knows, the folder will be receiving files from all over).
3) The code looks for the specified delimiter type anywhere in the whole file. This ultimately won't work for me because even if it is tab or space delimited, it may also have typed text in it that may have commas, spaces... maybe even tabs.

So I need a more bullet proof way. I did a little searching and saw that some folks had the idea of having the code only check the first row for tabs, spaces, commas, etc.... If the first row contain single-word headers, AND the first row contains equal number of words and delimiters.... then you can say with a higher degree of certainty what the delimiter really is. I could not find any actual code that does this, only discussion of it. I'll keep searching but let me know if you think that sounds like it would work?

Thanks again for taking a look at my attempts herein... :D

DocAElstein · Sep 10, 2014

Hi Sayre,
….

Sayre said:
Thanks a lot Rick! I was just getting ready to post again about this code. I've been playing with and noticed a couple things:………... :D

…..when the MrExcel experts appear it is like an appearance of God and we wonder how quickly they solve difficult problems. But like God they cannot be everywhere all the time. I will try to make a small contribution to bridge the gap.

Sayre said:
….
1) When I run this on my actual files I had to change this line of code:

Code:

Lines = Split(TotalFile, vbNewLine)

to this:

Code:

Lines = Split(TotalFile, vblf)

I was getting subscript out of range error in the VbNewLine version.

Now I fear I may have another variable to consider: Different text files could have different line-ending indicators! VbNewline, VbCrLf, VbLf... Is there a way to test for this as well as the delimiter type?

:D

. I could not reproduce your problem with any of the data we have from you. If you could supply a (shortened) sample of the problem data then we may be able to help further on that one.
. In the meantime, I think technically vbNewLine is a combination of VbCr and VbLf (Carriage return and line feed. - Makes sense!. In the practice, however, I have heard that often either does something similar. See here for example.
http://www.mrexcel.com/forum/excel-...ences-between-constants-vbcr-vblf-vbcrlf.html
or try a MrExcel / Google search – there are lots of similar Threads on the difference.

. On your general point of having different separators (delimiters) line-ending indicators etc… and coping with them.
. Rick’s code lends itself I think good to that. I think it is a sort of half way house between my original which relies on a sort of known / formatted input data format, and the sort of streaming ideas of apo’s scripting and my
ADOdb codes.
. Rick’s works on a similar principal to mine, except uses the binary input option which is some sort of very fundamental format which effectively comes out in a very long string. That lends itself nicely to searching for and replacing things like your delimiters, line-ending indicators etc.

To demonstrate with a very simple example. I made up a simple test file here
FileSnack | Easy file sharing
It is a small combination of your Tab and CSV delimitated text files so it has both comas and tabs on the same line. The following code reads that file in one go, by changing all the delimiters to one sort. You could similarly search and replace different line-ending indicators etc. or even combinations with other delimiters.

Sub ImportFile4() 'My modified for Sayre Dim R As Long, C As Long, FileNum As Long Dim TotalFile As String, PathAndFileName As Variant, Delim As String Dim Lines As Variant 'Kann not dimension more precisely as it will be created by a Split Dim Data() As Variant 'We know it is an array for our final data, but must be variant values Dim Fields As Variant If Application.CountA(Cells) Then MsgBox "You have data on the active worksheet... this code can only be run " & _ "when the active sheet is empty!", vbCritical, "Worksheet Not Empty" Exit Sub End If FileNum = FreeFile 'Give next "Highway" Number Free for data 'PathAndFileName = Application.GetOpenFilename("CSV Files(*.csv),*.csv,All Files (*.*),*.*", 1, "Open CSV File") 'Open File Dialogue Box Let PathAndFileName = ThisWorkbook.Path & "\SayreTextDataTABCSV.txt" Open PathAndFileName For Binary As #FileNum ' Open allocates a buffer, Binary means a very fundamental format, a long stream. If PathAndFileName = False Then Exit Sub TotalFile = Space(LOF(FileNum)) 'Binary format would said to be unformatted in comparison with other formats. A requirement then is that the accepting string is exactly the same length Get #FileNum, , TotalFile 'Put the whole long string in TotalFile string variable Close #FileNum 'Always good practice to close / shut everythiung off once finished 'So that is it-we have the entire file and just sort it out as we want it! TotalFile = Replace(TotalFile, ",", vbTab) Lines = Split(TotalFile, vbNewLine) 'Lines is made an array, values given by those seperated by a vbNewLine. It is a one dimensional array, effectively in the first column. Let Delim = vbTab For R = 0 To UBound(Lines) 'Go through each row Fields = Split(Lines(R), Delim) 'Fields becomes a 1 dimensional column with the lines split by the seperator. Effectively it is our colum but here as a row, so we call it a filed! For C = 0 To UBound(Fields) 'We are going along the row in the column, but each row is effestibvely wot we want as a column. Hence the Practice of calling it a field, which in everyday language usually translates to a row. for us it is the final column ReDim Preserve Data(1 To UBound(Lines) + 1, 1 To UBound(Fields) + 1) 'As an alternative to Rick Dimensionong with a guess at column numbers, I do it here as Here all info is available to get the size right. But note as it is continuosly being (unecerssary resize I must include preseve or each time any original data is lost. Data(R + 1, C + 1) = Fields(C) 'This puts the current Field value in the appropriate Rows and Column Next C ' a column of our final table is full, so go to next row Next R 'Go on to the next line or Range("A1").Resize(UBound(Data, 1), UBound(Data, 2)) = Data 'Wew resize to exactly the same size of Data. Then this will work (Works as long as Data is eqaul to or bigger than the new range) End Sub

. In the meantime I have learnt a bit and have another bit better version of my original code. I also modified it to take in that same text file

Sub DatensaetzeLesenSayre() On Error GoTo Fehler 'Particularly good idea when playing with open files to stop rather than crash on an error Open ThisWorkbook.Path & "\SayreTextDataTABCSV.txt" For Input As 3 'The Input option prepare the Input "Highway" 3 inn a recognisable structured way Dim FileLine As String 'In this method a line can be diectly accessed and put in a simple string Dim ExcelRow As Integer, ExcelColumn 'In this method we can directly write in Spreadsheet Let ExcelRow = 1 Dim Fields() As String 'A Simple Array for the Column Entries for a Row. Do Until EOF(3) 'We do until the End Of File is reached Line Input #3, FileLine 'A line can be read. (After this the next line for input is automatically set) FileLine = Replace(FileLine, vbTab, ",") Fields = Split(FileLine, ",") 'The line is split by the deliminator to a one dimensional Array in Excel Rows in one Column, but the Fields are actually our Column Entries for a Row (The current line) For ExcelColumn = 1 To UBound(Fields) + 1 'Going along Spreadsheet Columns Let ActiveSheet.Cells(ExcelRow, ExcelColumn).Value = Fields(ExcelColumn - 1) Next ExcelColumn Let ExcelRow = ExcelRow + 1 'This selects next excel Row Loop 'When row complete go and do again for next row(The file row will have already been set toi next row. Close 3 '"Highway" must be closed or some nasty errors can come in. Exit Sub Fehler: MsgBox (Err.Description) End Sub

.
. Note for simplicity, both codes do not ask for your file, they simply access directly that text file. So you should put it in the same folder as the file you put the new codes in .

. Alan
I’ll try to look in again soon

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

Sayre

Board Regular

Sayre

Board Regular

Excel Facts

Sayre

Board Regular

DocAElstein

Banned user

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

Sayre

Board Regular

Sayre

Board Regular

Excel Facts

Sayre

Board Regular

DocAElstein

Banned user

Rick Rothstein

MrExcel MVP

Sayre

Board Regular

DocAElstein

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