[X]My Assistant

[ecovindaloo]

I'm still getting the error message that the import spec can't be found. The same as yesterday.

All of the csv files will have the same fields and will be in the same field order.

Here's what I've tried so far:

Dim strTextLine As String
Dim aryMyData() As String
Dim strSQL As String
Dim directory As String
Dim FileName As String
Dim i As Integer



directory = "G:\MyStuff - HP\Consulting\ChicagoResearchCenter\NewLeads\"
FileName = "111Sleep study_Leads_2019-11-07_2019-11-12.csv"

i = 1

Open directory & FileName For Input As #1

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strTextLine ' Read line into variable.

aryMyData = Split(strTextLine, vbTab)

'create recordset and add data

End If
i = i + 1


Loop
Close #1

I'm able to get the file to open. When I use the immediate window to see what is in strTextLine the whole file is there including the header. I need to skip the header line and then parse the data line by line. This is where I'm having the issue now.

I've attached the csv file that I'm trying to work with.

Thanks again for all the help with this.

Attached File(s)

 CSV_Test.zip ( 2.55K )Number of downloads: 0

 

[ecovindaloo]

Here's where things are at now:

I pasted the text into a Notepad program to see what characters are in the text.

I'm using this line now to split the text: aryMyData = Split(strTextLine, vbLf). This is working and I can now go through the array line by line.

When I look at each line I need to break that up now. This is what the line looks like in Notepad:
l:2434622533453594 2019-11-08T16:05:53+00:00 ag:6148452742882 "Sleep study" as:6148452742082 EISAI c:6148452741482 EISAI f:2427067717542409 "Standard Lead Page No DOB" false fb drosen353@gmail.com p:+16307214271 David Rosen male "5641 N Kenmore" Chicago Illinois z:60660

If you paste the line above into a notepad program and look at the text there are two dots separating the fields. Any idea what they are so that I can split this line up?

[theDBguy]

Hi. You didn't answer the second part of my question. If you were still getting the same error, did you make sure the name of the saved import is correct?

In any case, if you still want to pursue the Freefile() approach, try splitting on vbCrLf, not just vbLf.

Hope it helps...

[ecovindaloo]

The name of the saved import I used in the code was the correct name. No clue why this wasn't working.

Originally I tried using the split function using vbcrlf and it didn't work. That's why I ended up using vbLF.

I just have to spend some time figuring out what delimiter to use for each row of the array I've created and then the rest of the process should be pretty straightforward.

[WildBird]

Hmm, but doesn't TransferText "requires" an import spec? At least, I thought so...

I am pretty sure that it is optional. Other way I have done it is a SQL statement and append to a staging table. Of course it requires all files to be the same structure.

Also done it with freefile and skipped the header by starting at 2nd line. Dont have code with me though, sorry.

[theDBguy]

Ah, it's optional for delimited files because it uses the default delimiter, but it's required for fixed-width files. Cheers!

[WildBird]

Ah, it's optional for delimited files because it uses the default delimiter, but it's required for fixed-width files. Cheers!

Last job I did, we couldnt get CSV files from 1 system, we had SSRS and I had code (in Excel) to extract out of there, and I had to change to pipe delimited, which was a hassle in itself, I then had to save it as txt file I think because Excel was doing something with the dates in it or something (cant remember, but couldnt use standard .CSV even though it was automated). These files then had to be encrypted and archived, and emailed as we had 2 environments and no sFTP site setup to enable transfer of files. I then had code on the other environment that would process the emails, and unencrypt them, and link and load them into staging tables. Had to have a separate import spec for each file type (40 or 50 different types) as they had different delimiters (had another system we got data from as well, which were CSV with a comma, not pipe) and code pages. Each file was opened up and edited to add another field into it, and used FreeFIle() I think to read and write back to themselves.

Basically, this was a lot of code and effort to setup. A standard CSV import should be easy :-)

"Should be" - famous last words!

Good luck with it.

[ecovindaloo]

Here's the function I created to be able to open a csv file and import the data into a temp table:

Public Function ExtractCSV_Data(strDirectory As String, strFilename As String)
'-----------------------------------------------------
'11/15/19 Import CSV data and input into a temp table
'-----------------------------------------------------
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strTextLine As String
Dim aryMyData() As String
Dim aryMyData2() As String
Dim strSQL As String
'Dim strDirectory As String
'Dim strFilename As String
Dim i As Integer
Dim j As Integer

On Error GoTo ExtractCSV_Data_Error

'strDirectory = "G:\MyStuff - HP\Consulting\ChicagoResearchCenter\NewLeads\"
'strFilename = "111Sleep study_Leads_2019-11-07_2019-11-12.csv"

Set db = CurrentDb
i = 1

Open strDirectory & strFilename For Input As #1

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strTextLine ' Read line into variable.

aryMyData = Split(strTextLine, vbLf)

For j = LBound(aryMyData) + 1 To UBound(aryMyData) - 1
aryMyData2 = Split(aryMyData(j), vbTab)

'Insert data into the temp table
strSQL = "SELECT * FROM tmpCSV_NewLeadsImport;"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.AddNew

.Fields(0) = aryMyData2(0)
.Fields(1) = aryMyData2(1)
.Fields(2) = aryMyData2(2)
.Fields(3) = aryMyData2(3)
.Fields(4) = aryMyData2(4)
.Fields(5) = aryMyData2(5)
.Fields(6) = aryMyData2(6)
.Fields(7) = aryMyData2(7)
.Fields(8) = aryMyData2(8)
.Fields(9) = aryMyData2(8)
.Fields(10) = aryMyData2(10)
.Fields(11) = aryMyData2(11)
.Fields(12) = aryMyData2(12)
.Fields(13) = aryMyData2(13)
.Fields(14) = aryMyData2(14)
.Fields(15) = aryMyData2(15)
.Fields(16) = aryMyData2(16)
.Fields(17) = aryMyData2(17)
.Fields(18) = aryMyData2(18)
.Fields(19) = aryMyData2(19)
.Fields(20) = aryMyData2(20)

.Update
End With

rst.close

Next
Loop

'Close file
Close #1

ExtractCSV_Data_Exit:
Set db = Nothing
Set rst = Nothing
Exit Function

ExtractCSV_Data_Error:
MsgBox Err.Description, , "ExtractCSV_Data"
Resume ExtractCSV_Data_Exit

End Function

This was a lot of work and the reason was because the csv file used a tab delimiter instead of a comma.

Thanks to everyone for all the help. Hopefully someone else can use this code down the line.

[theDBguy]

Hi. Congratulations! I'll make sure to point people here if they have the same problem. Good luck with your project.

[ecovindaloo]

Thanks. And thanks again for all the help.

[theDBguy]

Hi. You're welcome and thanks for sharing!