INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Access CSV file manipulation before import

VBA Access CSV file manipulation before import

(OP)
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.

RE: VBA Access CSV file manipulation before import

(OP)
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.

RE: VBA Access CSV file manipulation before import

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

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


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.

RE: VBA Access CSV file manipulation before import

(OP)
Example csv: attached

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

CODE --> VBA

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!

RE: VBA Access CSV file manipulation before import

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

CODE

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

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

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

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()
 

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?

0     Shortcode        
1     Keyword        
2     Type        
3     Volunteer Fundraiser        
4     Team        
5     Alternative Team ID        
6     Transaction Date        
7     Donation date     get the date into col2
8     Collected Amount  get the date into col3 and col5
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        
22    Donation          get the date into col1
23    Transaction ID    get the date into col4
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        
 

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

CODE

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

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, ",") 'Split text into array by comma
    
    strSQL = "INSERT INTO Donations2 ([ID No], [Date of Donation], Amount, [Check #], [Deductable Amount]) " _
        & " VALUES(" & aryMyData(22) & ", #" & aryMyData(7) & "#, " & aryMyData(8) & ", " & aryMyData(23) & ", " & aryMyData(8) & ")"
    
    'Debug.Print strSQL
    DoCmd.RunSQL strSQL
Loop
Close #1 



Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA Access CSV file manipulation before import

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.

RE: VBA Access CSV file manipulation before import

(OP)
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.

RE: VBA Access CSV file manipulation before import

(OP)
Andr,

Why bracket some of the column names?

RE: VBA Access CSV file manipulation before import

(OP)
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?

RE: VBA Access CSV file manipulation before import

"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 Date of Donation, you need the brackets around the name: [Date of Donation]

"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.

RE: VBA Access CSV file manipulation before import

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

RE: VBA Access CSV file manipulation before import

(OP)
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.

RE: VBA Access CSV file manipulation before import

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:

INSERT INTO SomeTable (NumberField, [Some other text field], [Date of hire])
VALUES(1234, 'Joe Brown', #1/2/2017#)


Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA Access CSV file manipulation before import

(OP)
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?

RE: VBA Access CSV file manipulation before import

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

RE: VBA Access CSV file manipulation before import

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:

CODE -->

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

CODE -->

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

RE: VBA Access CSV file manipulation before import

(OP)
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.

RE: VBA Access CSV file manipulation before import

" 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.

RE: VBA Access CSV file manipulation before import

(OP)

CODE --> VBA

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 

RE: VBA Access CSV file manipulation before import

(OP)
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

RE: VBA Access CSV file manipulation before import

I see you have your csv file "buried" pretty deep.
Copy it straight to C:\ or just to C:\Temp\ and point to that location and see if your code will work.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA Access CSV file manipulation before import

Do you have the same user account on both your laptop and the host computer?

RE: VBA Access CSV file manipulation before import

(OP)
ok, so this is the exact code that worked on my laptop. Her computer i changed the file location to a C: drive folder as Andrzejek suggested and i still couldn't get it to work. Filename was CSVUpload and file name was od.csv.

RE: VBA Access CSV file manipulation before import

By "couldn't get it to work" do you mean you crash on this line of code?

Open "C:\CSVUpload.csv" For Input As #1
or
Open "C:\od.csv" For Input As #1

Even thou the specified file(s) do exist in this location?

What error are you getting?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA Access CSV file manipulation before import

(OP)
neither
Open "C:\CSVUpload\od.csv" For Input As #1

ignore the comment talking about assuming test.csv is in c: drive. That's a copy paste thing from somewhere.

RE: VBA Access CSV file manipulation before import

(OP)
Basically, File not Found, and that was it.

RE: VBA Access CSV file manipulation before import

So what you are saying - you do have a folder on C:\ drive named CSVUpload and in that folder there is a file named od.csv.

And the line of code:
Open "C:\CSVUpload\od.csv" For Input As #1

gives you an error: File not found.

Is that correct?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA Access CSV file manipulation before import

Yes.

Here is code copied from the application computer.

CODE --> Access

Private Sub Command107_Click()
'Added by Greg Martin 08042017

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:\CSVupload\"
    FileName = "od.csv"
    i = 1
    Open directory & FileName For Input As #1
    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 Donations ([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 
http://www.tek-tips.com/viewthread.cfm?qid=1778974

RE: VBA Access CSV file manipulation before import

Sorry, this is gmart

RE: VBA Access CSV file manipulation before import

I assume your Command107 is an Online Upload command button, right?
If so, I would strongly recommend renaming it to something like cmdOnlineUpload

The only thing I can suggest with this error is to start completely new, fresh Access and try to run just this code:

CODE

Open "C:\CSVupload\od.csv" For Input As #1
Close #1 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA Access CSV file manipulation before import

(OP)
update:
I currently am running latest access on my laptop. opened up the database with no special conversion (database was created and maintained in Access 2003, and as i put in previous threads, it works. I setup another laptop with 2003 on it exclusively and brought the database over from the working laptop and the code worked on the new setup as well. So for some reason, i have not gotten in to work on the customers site box. This is crazy.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close