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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Linked Csv Table Opens As Empty Without Errors But The Csv File Has Data, Access 2016    
 
   
Uros
post Apr 3 2018, 11:04 PM
Post#1



Posts: 32
Joined: 26-November 15



Hi everyone, I've been stumped with an issue encountered this morning. I have a linked table in MS Access which is linked to a csv file on a network drive. Each week the csv file gets overwritten with a new file but the name stays the same. Queries in Access pull data from the linked table and feed a Tableau report. Previous two weeks everything worked fine, but this week when I open the linked table in Access it shows it as an empty table (1 of 1 records - just headers).

I went to linked table manager and re-point to the same file again in the same location to refresh and I get a message everything refreshed successfully. But when I open the table it again shows blank rows. So I try the same thing pointing to the previous week's csv file which I know worked fine, and it refreshes successfully but once again shows all rows blank. Both csv files have about 4k rows of data, and open in Excel without no apparent changes to column headers.

I tested importing the csv file again as a different table, and that works. But I cannot have this solution because it breaks the automation. The point is that a csv extract gets saved in a location on the network drive and that access reads that same file with updated data each time Tableau refreshes the MS Access query (a flow which worked for the last couple of iterations).

Any ideas?

Thanks

Uros
Go to the top of the page
 
Phil_cattivocara...
post Apr 4 2018, 02:32 AM
Post#2



Posts: 54
Joined: 2-April 18



Saying
QUOTE
I tested importing the csv file again as a different table, and that works.
do you always mean a linked table?
Just for test: can you try your actual database on a different PC, to exclude the problem is in your computer.
First step: try to recreate everything in a new database.
Second step: try to delete the linked table and recreate it.
IF (and I underline IF) the only way to get what you need is importing (read: linking) the csv file only recreating the linked table, you could write code to do this.
I would suggest not to open the original csv in any other way (you mentioned Excel, it's natural to do it), or at least in "read only" mode. Never with a word processor, only with text editor (like notepad or notepad++, for example) and never save any change. This is only for precaution.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
BruceM
post Apr 4 2018, 07:07 AM
Post#3


UtterAccess VIP
Posts: 7,322
Joined: 24-May 10
From: Downeast Maine


Working with a copy of the csv file, delete half the rows and try again. If the same result, try deleting the other half of the records from another copy, and try again.

Or try eliminating all but one row. If no luck, try the next row, and so forth for a few rows.

Or eliminate half of the fields.

The idea is to discover whether there are data problems that are causing the result you are seeing.

Is the csv file in a trusted location?

Do you have an old copy of the csv file, so you can see whether the problem is the data or the connection?
Go to the top of the page
 
Uros
post Apr 4 2018, 07:43 PM
Post#4



Posts: 32
Joined: 26-November 15



Hi, thanks for your replies Phil and Bruce. Yes I meant 'linked' table (instead of imported).

I think I discovered what the issue might have been, and it's related to your comment about opening csv files in Excel. I noticed that Excel opens the csv as a text file with all columns in perfect position. However Access (and Tableau) both opened the csv with columns names offset by one to two positions (i.e. the data in columns and column names were not matching. The last field displays as "Field38"). A couple of the column headers included commas AND quotation marks, for example [If "other", has an iSafe incident been raised?]. It's a bit of a nightmare to resolve, but Excel reads it all as one column whereas Access struggled. I think the reason for this is that the file was extracted as "CSV for Excel" from RTC (IBM Rational Team Concert) application, although the file extension is still jsut csv. This is also why when I compared previous version to the latest version of the csv in Excel, the column headers all seemed to match.

Adding to this, during the initial setup I renamed the csv file and pointed to it it again in "linked table manager" in Access, and it was opening fine in testing until the new version was saved yesterday. I still don't understand why no error messages came up. It is basically saying "yes, I can connect to the file successfully... but I'm reading nothing". I would have expected it to tell me that field names are not matching, like it would in an append query for example.

So I deleted the original setup, established a new link to the csv file (reading it as is - i.e. column names not in right position). I then set-up a query to 'translate' the file to the correct format before being processed further (which was a daunting task). So far it works, but I'll give it a few iterations before I can claim success.

Thank again.
This post has been edited by Uros: Apr 4 2018, 07:50 PM
Go to the top of the page
 
JonSmith
post Apr 5 2018, 05:15 AM
Post#5



Posts: 3,280
Joined: 19-October 10



I would like to also echo what was said about do not open CSV's in Excel. Excel will mess them up, its save as CSV function is garbage and loses data so badly aswell as frequently changes the structure.

In terms of getting this data into Access consistently. In my opinion I do not use linked tables, I find them to be an awful pain and import specs are really hard to expose and change resulting in multiple ones, having to remake tables, blah blah blah.

Instead I recommend connecting the to CSV directly in a query (you can put a connection string directly into a query), now you may correctly worry about how could Access possibly understand the file and what delimiter is used, what data typing etc and you'll surely end up with the same issues you have already, well you wouldn't be wrong to think that. The solution is to have a Schema.ini file. This will have all the details about the CSV explicitly spelled out (no more guessing Excel) and is automatically used by Access when reading the text file.

Its fast, dynamic and really reliable.
Go to the top of the page
 
BruceM
post Apr 5 2018, 07:19 AM
Post#6


UtterAccess VIP
Posts: 7,322
Joined: 24-May 10
From: Downeast Maine


QUOTE
I do not use linked tables

Do you mean as a general thing, or just in a situation such as this?
Go to the top of the page
 
JonSmith
post Apr 5 2018, 08:10 AM
Post#7



Posts: 3,280
Joined: 19-October 10



Ah, I was vague in that regard. I mean I do not use linked tables with CSV's.
Thanks for checking Bruce.
Go to the top of the page
 
BruceM
post Apr 5 2018, 08:25 AM
Post#8


UtterAccess VIP
Posts: 7,322
Joined: 24-May 10
From: Downeast Maine


I guessed that was what you meant. thumbup.gif
Go to the top of the page
 
zaxbat
post Apr 5 2018, 08:37 AM
Post#9



Posts: 642
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I'm guessing some errors are probably generated but maybe your have on error resume next and/or set warnings false.....

could try to catch the error and then adapt the code so it knows what to do next time this happens. I'm guessing you have non-display gobble-di-gook in the input file.
This post has been edited by zaxbat: Apr 5 2018, 08:39 AM

--------------------
A picture is worth a thousand words and a zipped DB is worth a thousand pictures.

Cheers! Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
JonSmith
post Apr 5 2018, 03:26 PM
Post#10



Posts: 3,280
Joined: 19-October 10



Dont think so zaxbat, the op is talking about linked tables and opening them and seeing no data. On Error resume next only suppresses errors in the specific VBA procedure being ran and it there is no indication of the OP using VBA to view the data and seeing the problem that is occurring.
Go to the top of the page
 
Uros
post Apr 15 2018, 10:27 PM
Post#11



Posts: 32
Joined: 26-November 15



Thanks for your reply and the suggestion for putting a connection string into a query. Could you please give an example of how I would do that with a Schema.ini file?

Regarding my previous post, I thought I had solved the problem by not opening the csv file in excel, but I'm still getting the same issue. Over the last week I've been deleting the table and then re-connecting to it again each time the data is refreshed. It seems to me that Access stores the info about the csv file somewhere with the timestamp in it. So each time this file is overwritten, despite the name of it not changing, it's getting 'confused' so opens the file as specified by the connection path but displays no data. I'm keen to try your solution of using a connection string in a query.

Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st April 2018 - 06:36 AM