UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Csv Import Issue, Access 2016    
 
   
ecovindaloo
post Nov 13 2019, 03:55 PM
Post#1



Posts: 286
Joined: 1-November 08
From: New York Area, USA


I have an Access front end with a MySQL backend. I'm trying to import csv files into a local Access table.

The csv file used the tab as a delimiter. Because of this I created a Saved Import and use it in a line of code.

This is the code:
DoCmd.TransferText acImportDelim, "CSV_FileImportOffice", "tmpCSV_NewLeadsImport", arrXL_Files(i), True

CSV_FileImportOffice is the name of the saved import.

The issue is that when I run the code I get an error message saying that the CSV_FileImportOffice can't be found. I'm able to manually run this saved import with no problem.

I also closed and reopened Access and still get the same error message.

Any idea why the code isn't working? Or is there a workaround for this type of issue?
Go to the top of the page
 
theDBguy
post Nov 13 2019, 04:21 PM
Post#2


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


Hi. I think you're using the wrong code. Try:

DoCmd.RunSavedImportExport "CSV_FileImportOffice"

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecovindaloo
post Nov 13 2019, 04:54 PM
Post#3



Posts: 286
Joined: 1-November 08
From: New York Area, USA


I created two import two saved imports one for my home office and one for the client. This code works at my home office, but not at the client.

So I don't think it's the code.
Go to the top of the page
 
theDBguy
post Nov 13 2019, 05:01 PM
Post#4


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


Hi. But, did you try my suggestion? Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecovindaloo
post Nov 13 2019, 05:07 PM
Post#5



Posts: 286
Joined: 1-November 08
From: New York Area, USA


I won't be able to try until tomorrow when I can setup a web meeting with my client.

I also have been trying this:
INSERT INTO tmpCSV_NewLeadsImport SELECT * FROM [Text;FMT=Delimited;HDR=YES;IMEX=2;CharacterSet=437;DATABASE=G:\MyStuff - HP\Consulting\ChicagoResearchCenter\NewLeads].[Sleep study_Leads_2019-11-07_2019-11-12.csv]

I get an error message "Syntax error in the FROM clause". Have you ever tried importing csv files this way?
Go to the top of the page
 
theDBguy
post Nov 13 2019, 05:08 PM
Post#6


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


I haven't, but I have seen it before. There's also another approach using an INI file.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecovindaloo
post Nov 13 2019, 05:12 PM
Post#7



Posts: 286
Joined: 1-November 08
From: New York Area, USA


In the big picture what I'm trying to do is the following:
1) Use code to get a list of files in a directory
2) Import data from each file into a local Access table
3) Move each file into an archive folder

This all works on my computer but I keep running into issues when I install a version for testing at the client site.

Can this be done using Freefile?
Go to the top of the page
 
theDBguy
post Nov 13 2019, 05:15 PM
Post#8


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


Hi. You can probably use Freefile() to do it, but it's also probably going to be more work.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecovindaloo
post Nov 13 2019, 05:25 PM
Post#9



Posts: 286
Joined: 1-November 08
From: New York Area, USA


I was debating about using FreeFile. I haven't used it in a while.

That's why I was hoping that someone would know why I was getting the original error about not being able to find the created saved import spec.
Go to the top of the page
 
theDBguy
post Nov 13 2019, 05:27 PM
Post#10


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (ecovindaloo)
That's why I was hoping that someone would know why I was getting the original error about not being able to find the created saved import spec.

I thought I told you why. You were using the wrong code/command/method. Try the one I gave you and let us know if it doesn't work.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecovindaloo
post Nov 13 2019, 05:35 PM
Post#11



Posts: 286
Joined: 1-November 08
From: New York Area, USA


I'll try it tomorrow and let you know what happens. Thanks for the help.
Go to the top of the page
 
theDBguy
post Nov 13 2019, 05:36 PM
Post#12


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


Okay. It should work; but if not, we'll help you figure it out. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecovindaloo
post Nov 13 2019, 05:36 PM
Post#13



Posts: 286
Joined: 1-November 08
From: New York Area, USA


Will your suggestion when I'm importing different files with different filenames?
Go to the top of the page
 
theDBguy
post Nov 13 2019, 05:39 PM
Post#14


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


Hi. The code I gave you executes a "saved import task," which was what I thought you said you had. Each saved import task specifies the file name and path, so it can't be used to import a different file, unless you save an import task for "all" the files you want to import. If you have no idea ahead of time what the name of the file will be, then you would want to use an import spec instead. An import spec is different than a saved import task.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecovindaloo
post Nov 13 2019, 07:05 PM
Post#15



Posts: 286
Joined: 1-November 08
From: New York Area, USA


I'll try creating an import spec tomorrow and let you know what happens.
Go to the top of the page
 
theDBguy
post Nov 13 2019, 07:49 PM
Post#16


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


Okay, if you create an import spec, then you'll have to use the original code you had earlier.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecovindaloo
post Nov 14 2019, 02:59 PM
Post#17



Posts: 286
Joined: 1-November 08
From: New York Area, USA


I dialed into the client and things still aren't working.

I tried to use this code: 'DoCmd.TransferText acLinkDelim, , "1111", arrXL_Files(i), True. It did create a linked table to the csv file but when I opened the linked file in Access there was only one field (there should have been nine fields), and it was pretty much worthless.

I'm thinking that I might have to use FreeFile to do this. I haven't used this in a long time.

Can anyone point me in the right direction to be able to do the following:
1) Open the csv file as a FreeFile
2) Go row by row and parse each row using the tab delimiter

I will create a recordset pointing to the table I want to enter this data into. Then I will use the addnew for the recordset and enter data row by row.
Go to the top of the page
 
theDBguy
post Nov 14 2019, 03:21 PM
Post#18


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


Hi. Did you try the code I suggested? If so, what was the error message? If the same as the one in your original post, then are you sure the saved import name is correct?

Before we go the Freefile() route, can you please confirm if all the CSV files have the same structure? i.e. number of columns, data types, etc.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
WildBird
post Nov 14 2019, 03:35 PM
Post#19


UtterAccess VIP
Posts: 3,677
Joined: 19-August 03
From: Auckland, Little Australia


I've worked with a lot of CSVs, and as long as they are the same structure, I havent had any issue with them. Rarely use the import specs (Last contract I had to due to differences and a few other things), I just usually use DoCmd.TransferText and a File System Object to loop a folder.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
theDBguy
post Nov 14 2019, 03:38 PM
Post#20


UA Moderator
Posts: 76,882
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (WildBird)
I've worked with a lot of CSVs, and as long as they are the same structure, I havent had any issue with them. Rarely use the import specs (Last contract I had to due to differences and a few other things), I just usually use DoCmd.TransferText and a File System Object to loop a folder.
Hmm, but doesn't TransferText "requires" an import spec? At least, I thought so...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 11:22 AM