VBA Access CSV file manipulation before import 1

[gmart]

I have a .csv file that has information i want to import. I tried importing the present csv into a table and moving data via Insert Into Select statement but couldn't get it to work. The easiest route it seems is to take the csv file and format it to match the table i want to move it to. Then the sql move will be easier. I have done similar file manipulation using Excel VBS but there are some built in features that are not present. Can someone show me some examples, or point me in the right direction.

 

[gmart]

So i basically need to search through each column header looking for 1 of 5 strings. Once there is a match i grab all data under that header.

 

[Andrzejek]

An example of your csv file would be nice, like:

[pre]
ABC,XYZ,JKL
123,abc,tyu
666,Joe,Smith
876,Barb,Brown
[/pre]
And if you find [tt]XYZ[/tt] in the header, you want to grab (?)
[tt]abc
Joe
Barb[/tt]

and do (what) with it.

If you have any code - show that as well.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[gmart]

Example csv: attached

I've changed the approach a bit. here is what i have been working on today.

Dim filepath As String
Dim sqlinsert As String
Dim sqlvalue As String
Dim sqlquery As String
Dim sqlwhere As String

'Set db = CurrentDb
directory = "C:\Users\gmartin\Desktop\Equip\The Soup Kitchen\Database\"
FileName = "onlinedonations.csv" 'Assuming test.csv is in C:\ directory
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & directory & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
strSQL = "SELECT * FROM " & FileName
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst
Do
   col1 = rs("Donation")
   col2 = rs("Donation Date")
   col3 = rs("Collected Amount")
   col4 = rs("Transaction ID")
   col5 = rs("Collected Amount")

    sqlinsert = "INSERT INTO [Donations2](ID No,Date of Donation,Amount,Check #,Deductable Amount,Account,In Memory of,From Newsletter?,From Spring Newsletter,From Summer Newsletter?,From Fall Newsletter?,From Winter Newsletter?,Receipt - Cash,Item Donated,Receipt - Item,Donation Type,Reissue Receipt Letter,Retrieve,Record Date)"
    sqlvalue = " Values (" & col1 & "," & col2 & "," & col3 & "," & col4 & "," & col5 & ",0000,,No,No,No,No,No,Y,,,online,No,No," & Now() & ")"
  '  sqlwhere = col4
    sqlquery = sqlinsert & sqlvalue
    MsgBox sqlquery
    DoCmd.RunSQL sqlquery

   rs.MoveNext
Loop Until rs.EOF

Still working through the SQL syntax. Don't have the strings with "'" around them yet.

Basically im treating the csv file like a database, pulling the data row by row and running a Docmd.runsql every row.

Thank you for your time on this!

 

[Andrzejek]

So you want to grab [blue]"Collected Amount"[/blue] twice and insert the values into [blue]col3[/blue] and [blue]col5[/blue]

Do
   col1 = rs("Donation")
   col2 = rs("Donation Date")[blue]
   col3 = rs("Collected Amount")[/blue]
   col4 = rs("Transaction ID")[blue]
   col5 = rs("Collected Amount")[/blue]

and then use those variables to insert the same data into [tt]Amount[/tt] and [tt]Deductable Amount[/tt] fields in your [tt]Donations2[/tt] table?

And also it looks to me you want to Insert a lot of hard-coded values into your table:

[pre]
Account 0000
In Memory of -empty string-
From Newsletter? No
From Spring Newsletter No
From Summer Newsletter? No
From Fall Newsletter? No
From Winter Newsletter? No
Receipt - Cash Y
Item Donated -empty string-
Receipt - Item -empty string-
Donation Type online
Reissue Receipt Letter No
Retrieve No
Record Date Now()
[/pre]

You may want to set those fields with the default values so don't have to mention them at all when inserting a record into that table. Your Insert statement will be a lot shorter and cleaner.

So basically you want to grab just the 4 pieces out of your cvs file and put it into your variables for your INSERT statement, right?

[pre]
0 Shortcode
1 Keyword
2 Type
3 Volunteer Fundraiser
4 Team
5 Alternative Team ID
6 Transaction Date [blue]
7 Donation date get the date into col2
8 Collected Amount get the date into col3 and col5[/blue]
9 Pledged amount
10 Cc type
11 last_4
12 Phone
13 First name
14 Last name
15 Street address
16 City
17 State
18 Zip
19 Email
20 Billing status
21 Billing type [blue]
22 Donation get the date into col1
23 Transaction ID get the date into col4[/blue]
24 Source
25 Form
26 Form Name
27 Form Type
28 Fulfillment Calls
29 Fulfillment Texts
30 Donation notes
31 Account
32 Account ID
33 Campaign name
34 Account Plan
35 Account Plan Price
36 Frequency
37 Anonymous
38 Billing transaction
39 Billing transaction reference
40 Billing response code
41 Parent Name
42 Location
[/pre]

If that's the case, all what you need is this:

Dim strTextLine As String
Dim aryMyData() As String
Dim strSQL As String

Open directory & FileName For Input As #1
Do While Not EOF(1)             [green]' Loop until end of file.[/green]
    Line Input #1, strTextLine   [green]' Read line into variable.[/green]
    aryMyData = Split(strTextLine, ",") [green]'Split text into array by comma[/green]
    
    strSQL = "INSERT INTO Donations2 ([ID No], [Date of Donation], Amount, [Check #], [Deductable Amount]) " _
        & " VALUES(" & aryMyData(22) & ", #" & aryMyData(7) & "#, " & aryMyData(8) & ", " & aryMyData(23) & ", " & aryMyData(8) & ")"
    [green]
    'Debug.Print strSQL[/green]
    DoCmd.RunSQL strSQL
Loop
Close #1

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[sxschech]

A note on the sql approach: I have found that it doesn't handle long text, so any field that is over 255 chars will be truncated. Maybe someone knows a work around for that, but I haven't come across it, so for cases where I have to import more than 255 chars, I am unable to use that technique.

 

[gmart]

Andr,

Im doing this for our soup kitchen. The database was designed by someone else so im stuck with table design for now because of the heavy integration nature of the present database. The csv file format is basically canned long format export from a donations website. Just looking to give her, Merry, a method to slurp up her online donations. The next issue will be to guarantee unique records based on transaction ID. Haven't done alot with VBA and SQL. THanks for your support. I'll let you know if it works.

sxs,

are you referring to the amount of information in a variable? or the amount of information coming from the database.

 

[gmart]

Andr,

Why bracket some of the column names?

 

[gmart]

Andr,

Ok, so i don't have a column of numbers in the csv file. Are you asking that i add them to the file then run this or are those numbers based on the comma delimiting?

 

[Andrzejek]

Why bracket some of the column names?"
Access allows you to create table names and field (column) names with spaces (and special characters like # and ?) and you can even use reserved words in Access.. Just because you can, does not mean you should.
So if the name of the field in the table is [tt]Date of Donation[/tt], you need the brackets around the name: [tt][Date of Donation][/tt]

"The database was designed by someone else so im stuck with table design "
That's fine, we can work with this.
My main point was to show you how to:
1. Read a text file line-by-line
2. Split the line of text with comma as delimiter
3. Create an array of values from your CSV file (see 1 and 2 above)
4. Use some elements of an array in your INSERT statement.


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[gmart]

Andr,
Also, what is the # sign for? I have never seen that in SQL queries. Also, i agree, there should be a sarcasm font

 

[gmart]

Runtime error '3075'
Syntax error (missing operator) in query expression 'Collected Amount'

Put some logic in to skip the first line read before it hits docmd and it worked until...

Syntax error is query expression '$1.00' So im putting a currency into a number data type. Are there any quick ways around this?

Also, are there any good documentation on file manipulation? Thank you for your help.

 

[Andrzejek]

How is the field declared in your table where you want to enter $1.00 - a number? A text?

# sign in Access is used for Dates

Number is just a number: 1234
Text - you need single quotes around the value: 'Joe Brown'
Dates: #1/2/2017#

An example of the INSERT statement would look like:
[tt]
INSERT INTO SomeTable (NumberField, [Some other text field], [Date of hire])
VALUES(1234, 'Joe Brown', #1/2/2017#)[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[gmart]

the destination table column is designated number. I know of the difference in data types. Is there a quick way around it. like something that knocks off the first digit of a variable, or some magic conversion tool?

 

[gmart]

Ok, got it. Just took out the '$' using right function. Thanks again for your help.

 

[sxschech]

Sorry to get back late on answering your question about the 255 chars in the SQL link. That refers to the number of chars in an individual field not the total amount of data that can be read. For example if you had 4 fields and Notes contained a paragraph of 300 characters, then anything after 255 would not be added to the field, ImportDate would contain the date as this is a different field. Using less chars for illustration, only to demo what would happen:

FirstName, LastName, Notes, ImportDate
Bill, Ernsall, Bill joined the group back in 1998 and has contributed much, "05/05/2017"

FirstName:Bill
LastName: Ernsall
Notes: Bill joined the group bac
ImportDate: 05/05/2017

 

[gmart]

Ok, i got this to work on my laptop. When i transferred to the host computer i could even get the open directory to find the file. Help. I have the latest and greatest Access on my laptop but its Access 2003. Is there a functionality that i need...a library or something.

 

[Andrzejek]

i could [not?] even get the open directory to find the file." - and how were you doing it?

By now nobody knows / remembers (it is true about me, at least) what code you are trying to run.

Post your code (with TGML tags) and any errors you get.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[gmart]

Private Sub Command107_Click()
On Error GoTo Err_Command107_Click

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


    directory = "C:\Users\gmartin\Desktop\Equip\The Soup Kitchen\Database\"
    FileName = "onlinedonations.csv" 'Assuming test.csv is in C:\ directory
    i = 1
    Open directory & FileName For Input As #1  **********STOPPED HERE************
    DoCmd.SetWarnings False  'TURN ERROR PROMPTS OFF
    Do While Not EOF(1)             ' Loop until end of file.
        Line Input #1, strTextLine   ' Read line into variable.
        aryMyData = Split(strTextLine, ",") 'Split text into array by comma
      If i <> 1 Then
        i2 = Len(aryMyData(8))
        currtoint = Right(aryMyData(8), i2 - 1)
        i3 = Int(currtoint)
        
        strSQL = "INSERT INTO Donations2 ([ID No], [Date of Donation], Amount, [Check #], [Deductable Amount], [Donation Type]) " _
            & " VALUES(" & aryMyData(22) & ", #" & aryMyData(7) & "#, " & i3 & ", " & aryMyData(23) & ", " & i3 & ",'Online')"
        
        
        DoCmd.RunSQL strSQL
         
     End If
      i = 2
    Loop
    Close #1
    DoCmd.SetWarnings True

Exit_Command107_Click:
    DoCmd.SetWarnings True
    Exit Sub

Err_Command107_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume Exit_Command107_Click
    
End Sub

 

[gmart]

Ok, kept getting a 'Couldn't find file' I checked and checked. Even copy pasted the filename. The code i posted worked on my laptop. On install i changed the directory and the filename.

Was thinking since it was Access 2003 there was some missing dll or library inclusion that would help.

Thanks,

Greg