Import Multiple csv file into Access Table

[jcvd_76]

Hi all,
this is the first time i write in a forum.I've a problem with VBA and i hope someone of you can help me.
I use this piece of code to import some csv files into one new access table.

function ImportCSV()
dim input_file as variant

input_file = Dir("c:\temp\*.csv", vbSystem)

do until input_file = vbNullString
Docmd.TransferText acImportDelim, "spec", "table name","c:\temp\" & input_file, False
loop
end function

Is there a way to insert into the new table also the name of the origin sheet?(like a field)
Thanks a lot in advance,
jcvd_76

 

[WayneRyan]

jcvd,

Just a guess here ...

Public Function ImportCSV()
Dim input_file as variant
Dim TableName As String  <-- New

input_file = Dir("c:\temp\*.csv", vbSystem)

Do Until input_file = vbNullString
   TableName = Mid(input_file, 1, Instr(1, input_file, ".") - 1) <-- New
   Docmd.TransferText acImportDelim, "spec", TableName, "c:\temp\" & input_file, False
   Loop
End Function

Wayne

 

[jcvd_76]

Hi WayneRyan,
thanks for your help.
But I think that i've not been clear in explain my problem.Excuse me.Now I try.
I need to import in the destination table also the name of the sheet as field.
For Example imagine I've some csv with the streets of every town.The name of the files are the names of the town.
When i import these files i need to know the relative town of the streets else i can't understand the rispective town.

Milano.csv
London.csv
Paris.csv

inside, every sheet has the street "Tokyo street,15"
i need to insert in the table name these fields.

Milano | Tokyo street,15
London | Tokyo street,15
Paris | Tokyo street,15

so i know every street at which town is relative.
Thanks a lot for your help,
jcvd