I have some csv files. How many is not known and the amount of data on them
vary - but they all have trhe same format (same number of colums).

I will need to import all the data on those file one by one to an excel sheet.

Can that be done even though I dont know how many csv file are there in my
folder?

Many thanks.
Raphael

Option Explicit

Sub GetData()

Dim rowindex As Long
Dim sFile As String
Dim source As String
Dim fso As Object
Dim oFile As Object
Dim txt As Object
Dim text

Set fso = CreateObject("Scripting.FileSystemObject")
source = "s:\"

For Each oFile In fso.GetFolder(source).Files

If UCase(Right(oFile.Name, 4)) = ".CSV" Then

Set txt = fso.OpenTextFile(source & "\" & oFile.Name)
With txt
Do Until .AtEndOfStream
text = .ReadLine
rowindex = rowindex + 1
If rowindex 65000 Then
MsgBox "TOO MANY LINES!!"
Exit Sub
End If
Cells(rowindex, 1) = text
Loop
.Close
End With
Set txt = Nothing
End If

Next

End Sub


"Dan" wrote:

I have some csv files. How many is not known and the amount of data on them
vary - but they all have trhe same format (same number of colums).

I will need to import all the data on those file one by one to an excel sheet.

Can that be done even though I dont know how many csv file are there in my
folder?

Many thanks.
Raphael

great, many thanks

"Patrick Molloy" wrote:

Option Explicit

Sub GetData()

Dim rowindex As Long
Dim sFile As String
Dim source As String
Dim fso As Object
Dim oFile As Object
Dim txt As Object
Dim text

Set fso = CreateObject("Scripting.FileSystemObject")
source = "s:\"

For Each oFile In fso.GetFolder(source).Files

If UCase(Right(oFile.Name, 4)) = ".CSV" Then

Set txt = fso.OpenTextFile(source & "\" & oFile.Name)
With txt
Do Until .AtEndOfStream
text = .ReadLine
rowindex = rowindex + 1
If rowindex 65000 Then
MsgBox "TOO MANY LINES!!"
Exit Sub
End If
Cells(rowindex, 1) = text
Loop
.Close
End With
Set txt = Nothing
End If

Next

End Sub


"Dan" wrote:

I have some csv files. How many is not known and the amount of data on them
vary - but they all have trhe same format (same number of colums).

I will need to import all the data on those file one by one to an excel sheet.

Can that be done even though I dont know how many csv file are there in my
folder?

Many thanks.
Raphael

Patrick, not being a frequent writer of Excel macros I'm not a guru when it
comes to reading them. Perhaps you can tell me if this sampale will do what
I'm looking for.

I frequently have a set of CSV files; no specific number but they would be a
range (e,g., LD011 to LD055) in a folder or I could move the ones I want to a
folder. The fields are truly comma seperated.

I want to load them all into one spreadsheed, with each in its own page,
ideally with the file name being used to set the name on the page tab.

It isn't unusual to have 50 to 100 of these in one set, so doing this
manually importing one at a time, renaming the tab, etc. is boring, time
consuming and prone to errors. Merging all the files into one and impporting
that is a non-starter.

Your sample code would likely take some change to at least handle the tab
label thing, but would it do the importing of the CSVs into individual pages?

I've seen discussions here on this basic subject and your name pops up
frequently in addressing the questions.

Any insiight would be appreciated!

"Patrick Molloy" wrote:

Option Explicit

Sub GetData()

Dim rowindex As Long
Dim sFile As String
Dim source As String
Dim fso As Object
Dim oFile As Object
Dim txt As Object
Dim text

Set fso = CreateObject("Scripting.FileSystemObject")
source = "s:\"

For Each oFile In fso.GetFolder(source).Files

If UCase(Right(oFile.Name, 4)) = ".CSV" Then

Set txt = fso.OpenTextFile(source & "\" & oFile.Name)
With txt
Do Until .AtEndOfStream
text = .ReadLine
rowindex = rowindex + 1
If rowindex 65000 Then
MsgBox "TOO MANY LINES!!"
Exit Sub
End If
Cells(rowindex, 1) = text
Loop
.Close
End With
Set txt = Nothing
End If

Next

End Sub


"Dan" wrote:

I have some csv files. How many is not known and the amount of data on them
vary - but they all have trhe same format (same number of colums).

I will need to import all the data on those file one by one to an excel sheet.

Can that be done even though I dont know how many csv file are there in my
folder?

Many thanks.
Raphael

Hi NormD

I want to load them all into one spreadsheed, with each in its own page,

Check out
http://www.rondebruin.nl/txtcsv.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"NormD" wrote in message ...
Patrick, not being a frequent writer of Excel macros I'm not a guru when it
comes to reading them. Perhaps you can tell me if this sampale will do what
I'm looking for.

I frequently have a set of CSV files; no specific number but they would be a
range (e,g., LD011 to LD055) in a folder or I could move the ones I want to a
folder. The fields are truly comma seperated.

I want to load them all into one spreadsheed, with each in its own page,
ideally with the file name being used to set the name on the page tab.

It isn't unusual to have 50 to 100 of these in one set, so doing this
manually importing one at a time, renaming the tab, etc. is boring, time
consuming and prone to errors. Merging all the files into one and impporting
that is a non-starter.

Your sample code would likely take some change to at least handle the tab
label thing, but would it do the importing of the CSVs into individual pages?

I've seen discussions here on this basic subject and your name pops up
frequently in addressing the questions.

Any insiight would be appreciated!

"Patrick Molloy" wrote:

Option Explicit

Sub GetData()

Dim rowindex As Long
Dim sFile As String
Dim source As String
Dim fso As Object
Dim oFile As Object
Dim txt As Object
Dim text

Set fso = CreateObject("Scripting.FileSystemObject")
source = "s:\"

For Each oFile In fso.GetFolder(source).Files

If UCase(Right(oFile.Name, 4)) = ".CSV" Then

Set txt = fso.OpenTextFile(source & "\" & oFile.Name)
With txt
Do Until .AtEndOfStream
text = .ReadLine
rowindex = rowindex + 1
If rowindex 65000 Then
MsgBox "TOO MANY LINES!!"
Exit Sub
End If
Cells(rowindex, 1) = text
Loop
.Close
End With
Set txt = Nothing
End If

Next

End Sub


"Dan" wrote:

I have some csv files. How many is not known and the amount of data on them
vary - but they all have trhe same format (same number of colums).

I will need to import all the data on those file one by one to an excel sheet.

Can that be done even though I dont know how many csv file are there in my
folder?

Many thanks.
Raphael

That looks pretty darn good, Ron! I'll give it a try and let you know what I
find.

If I save an "empty" sheet with the macto added, I should be able to open it
whenever and run the macro to load in a new batch of files, yes? The is a
fairly frequent operation so it is something I want to keep handy.

Thank you for your help!

"Ron de Bruin" wrote:

Hi NormD

I want to load them all into one spreadsheed, with each in its own page,

Check out
http://www.rondebruin.nl/txtcsv.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"NormD" wrote in message ...
Patrick, not being a frequent writer of Excel macros I'm not a guru when it
comes to reading them. Perhaps you can tell me if this sampale will do what
I'm looking for.

I frequently have a set of CSV files; no specific number but they would be a
range (e,g., LD011 to LD055) in a folder or I could move the ones I want to a
folder. The fields are truly comma seperated.

I want to load them all into one spreadsheed, with each in its own page,
ideally with the file name being used to set the name on the page tab.

It isn't unusual to have 50 to 100 of these in one set, so doing this
manually importing one at a time, renaming the tab, etc. is boring, time
consuming and prone to errors. Merging all the files into one and impporting
that is a non-starter.

Your sample code would likely take some change to at least handle the tab
label thing, but would it do the importing of the CSVs into individual pages?

I've seen discussions here on this basic subject and your name pops up
frequently in addressing the questions.

Any insiight would be appreciated!

"Patrick Molloy" wrote:

Option Explicit

Sub GetData()

Dim rowindex As Long
Dim sFile As String
Dim source As String
Dim fso As Object
Dim oFile As Object
Dim txt As Object
Dim text

Set fso = CreateObject("Scripting.FileSystemObject")
source = "s:\"

For Each oFile In fso.GetFolder(source).Files

If UCase(Right(oFile.Name, 4)) = ".CSV" Then

Set txt = fso.OpenTextFile(source & "\" & oFile.Name)
With txt
Do Until .AtEndOfStream
text = .ReadLine
rowindex = rowindex + 1
If rowindex 65000 Then
MsgBox "TOO MANY LINES!!"
Exit Sub
End If
Cells(rowindex, 1) = text
Loop
.Close
End With
Set txt = Nothing
End If

Next

End Sub


"Dan" wrote:

I have some csv files. How many is not known and the amount of data on them
vary - but they all have trhe same format (same number of colums).

I will need to import all the data on those file one by one to an excel sheet.

Can that be done even though I dont know how many csv file are there in my
folder?

Many thanks.
Raphael

Yes you can copy the code in a workbook and if you need it
open that workbook and run the code.

Or add it to your personal.xls file

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"NormD" wrote in message ...
That looks pretty darn good, Ron! I'll give it a try and let you know what I
find.

If I save an "empty" sheet with the macto added, I should be able to open it
whenever and run the macro to load in a new batch of files, yes? The is a
fairly frequent operation so it is something I want to keep handy.

Thank you for your help!

"Ron de Bruin" wrote:

Hi NormD

I want to load them all into one spreadsheed, with each in its own page,

Check out
http://www.rondebruin.nl/txtcsv.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"NormD" wrote in message ...
Patrick, not being a frequent writer of Excel macros I'm not a guru when it
comes to reading them. Perhaps you can tell me if this sampale will do what
I'm looking for.

I frequently have a set of CSV files; no specific number but they would be a
range (e,g., LD011 to LD055) in a folder or I could move the ones I want to a
folder. The fields are truly comma seperated.

I want to load them all into one spreadsheed, with each in its own page,
ideally with the file name being used to set the name on the page tab.

It isn't unusual to have 50 to 100 of these in one set, so doing this
manually importing one at a time, renaming the tab, etc. is boring, time
consuming and prone to errors. Merging all the files into one and impporting
that is a non-starter.

Your sample code would likely take some change to at least handle the tab
label thing, but would it do the importing of the CSVs into individual pages?

I've seen discussions here on this basic subject and your name pops up
frequently in addressing the questions.

Any insiight would be appreciated!

"Patrick Molloy" wrote:

Option Explicit

Sub GetData()

Dim rowindex As Long
Dim sFile As String
Dim source As String
Dim fso As Object
Dim oFile As Object
Dim txt As Object
Dim text

Set fso = CreateObject("Scripting.FileSystemObject")
source = "s:\"

For Each oFile In fso.GetFolder(source).Files

If UCase(Right(oFile.Name, 4)) = ".CSV" Then

Set txt = fso.OpenTextFile(source & "\" & oFile.Name)
With txt
Do Until .AtEndOfStream
text = .ReadLine
rowindex = rowindex + 1
If rowindex 65000 Then
MsgBox "TOO MANY LINES!!"
Exit Sub
End If
Cells(rowindex, 1) = text
Loop
.Close
End With
Set txt = Nothing
End If

Next

End Sub


"Dan" wrote:

I have some csv files. How many is not known and the amount of data on them
vary - but they all have trhe same format (same number of colums).

I will need to import all the data on those file one by one to an excel sheet.

Can that be done even though I dont know how many csv file are there in my
folder?

Many thanks.
Raphael