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
> Import Export Specs Missing, Access 2016    
 
   
WildBird
post Sep 12 2019, 07:19 PM
Post#1


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


Hi All,

I have been working with a file for months now, have about 160 odd Import Export Specs set up, files are a mix of CSVs with commas, and pipes delimited, and a mix of unicode and mix of text qualifiers.

Just tried importing a file again, and discovered that it wouldn't let me, saying it couldn't find File Spec. Went to MSysIMEXSpecs and there was only 3 entries.

Anyone else had this happen to them?

File is run locally, as I am only one using it.

I am looking back through backups now, I am hoping I find a table that has them all in MSysIMEXSpecs table and hope I can reimport them. Will also need MSysIMEXColumns as well of course.

Not really looking forward to rebuilding all specs again manually.........

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Sep 12 2019, 10:17 PM
Post#2


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


Had to go through backups one by one checking the MSysIMEXSpecs. I now backup several times a day, so had over 450 backups. Was about 8 days ago. I renamed the existing system tables, and deleted them, and reimported from latest file with records. All I can do is try to reimport all files again, and see where it crashes. Hasslesome, but better than re importing and building each one by one.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
JonSmith
post Sep 13 2019, 02:51 AM
Post#3


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



I find that import specs in Access are not really ideal. I personally find that importing CSV's is handled much much better by writing a schema.ini file and directly linking to the csv in SQL. This way you can choose to not even bother importing the data if you dont need it and can manipulate the data before import too. Its alot more stable and flexible if you ask me.


JS
Go to the top of the page
 
jleach
post Sep 13 2019, 05:38 AM
Post#4


UtterAccess Editor
Posts: 10,145
Joined: 7-December 09
From: St Augustine, FL


I'm with Jon on this one. Scheme.ini seems to be far more stable, I've never really trusted the import/export specs stored in Access itself.

Sometimes I'll link to the CSV then run the ConvertToLocalTable (either manually or via DoCmd.MenuItem). For whatever reason, that internal conversion seems to do the best job, then I can run a query to massage the data into it's final table, but of course that all depends on your workflow (sounds like you're working with a lot of files).

Sometimes if things are really ugly, I'll do a plain old file read and run a custom parser.

--------------------
Go to the top of the page
 
WildBird
post Sep 15 2019, 03:59 PM
Post#5


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


Thanks guys. Its a one off basically, a data migration. Should only be a few weeks to go, and just now in the last few weeks it is giving these sort of issues.

45 or so files from one system, and 36 or so from another system. The import and export specs had been working fine, no idea how the data got wiped out of the tables.

In hindsight, would look at schema.ini file. What I wanted to do was use SSIS, and SQL, but couldn't get SQL Server due to political reasons.....Just don't have time to rewrite everything now with time frames being very tight.

Came in on weekend and wrote an audit function that will write entries, such as table and query defs count, code lines count, and also the record counts of the system Import Export tables. Will monitor this and see if anything changes.

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 02:28 AM