Jeavo
(Ryan Jeavons)
April 1, 2019, 1:32pm
1
Hi
I’m currently attempting to transfer my data from an Excel file or CSV to an MS Access DB
I have currently set up:
-Connecting to MS Access file
-Read my CSV File
-Output my CSV to a Data Table variable
The parts I’m struggling and would like some advise with are:
-“For each row” and assigning my rows and how it works
-Using the correct “Insert” query
Using "Insert into ‘Name of my table’ doesn’t seem to get picked up properly.
Hope this explains my issue clearly.
Thanks
pavanh003
(Pavan Harisha)
April 1, 2019, 2:04pm
2
Hi,
instead of using inside for each row activity directly use the insert into activity and give the datatable name and the destination table name ie data base table name.
Let us know if this helps.
Regards,
Pavan H
Jeavo
(Ryan Jeavons)
April 1, 2019, 2:09pm
3
Hi @pavanh003 thanks for the reply
Here’s what I’m getting
pavanh003
(Pavan Harisha)
April 1, 2019, 2:14pm
4
Hi,
The issue is with connecting to database please check your connection wizard.
Or may be the permission to update the table in database.
Regards,
Pavan H
pavanh003
(Pavan Harisha)
April 1, 2019, 2:16pm
5
Hi,
What is the datatype of DT1Row?
why are you using that variable?
let us know,
Regards,
Pavan H
Jeavo
(Ryan Jeavons)
April 1, 2019, 2:30pm
6
Hi @pavanh003
I have been following someone’s guide and may have gone wrong. This is what I’ve been struggling with. Is there another way for me to define my rows then?
When I attempt to connect to my database I select the correct file my database is ran from and test the connection which seems to connect fine. Am I doing this correctly?
I’ve attached images to show.
Thanks
pavanh003
(Pavan Harisha)
April 1, 2019, 2:31pm
7
Hi,
What is the datatable you are passing to insert activity
Regards,
Pavan H
pavanh003
(Pavan Harisha)
April 1, 2019, 2:48pm
9
Hi,
Try follow this link and let us knows if this helps.
Hi
I have experienced the following error when trying to use the activity ‘Execute Query’:
Message: Cannot create and populate list type System.Data.Odbc.OdbcErrorCollection. Path ‘$values’, line 1, position 175.
Source: NewtonSoft.Json
Exception Type: JsonSerializationException
Have any of you seen this error before and knows how to fix it?
Best regards,
Jan
Regards,
Pavan H
Jeavo
(Ryan Jeavons)
April 1, 2019, 2:53pm
10
@pavanh003 I don’t believe the issue with connecting to the database as no errors throw in the first instance.
The error seems to be with my “Insert Into” syntax.
Is there a standard query I can use to update all rows from my CSV into the DB?
Thanks
Jeavo
(Ryan Jeavons)
April 1, 2019, 2:57pm
11
Or, is there a way for me to
-Connect to databse
-Read CSV
-Build datatable
-Output Datatable
-Insert
-Disconnect
Cheers
1 Like
pavanh003
(Pavan Harisha)
April 1, 2019, 3:01pm
12
Hi,
Read csv.
insert activity to add to database (configure wizard to connect to database).
the output will be of type int which will say number of rows affected.
Or try with connect and use that connection string in insert activity connection string property.
let us know if this helps.
Regards,
Pavan H
pavanh003
(Pavan Harisha)
April 1, 2019, 3:02pm
13
This way also should work
1 Like