How to import file specs from access to excel

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

How to import file specs from access to excel

Post by Michael Abrams »

28 million results in Google.

Zero real world solution (that I could find)

I would like to import a file spec from Access into Excel

I cannot figure out how.

Thank you for any direction you have for me.

Michael

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to import file specs from access to excel

Post by HansV »

Can you explain what you mean by "importing a file spec"?
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

I have a text file which is fixed width ( about 50+ fields)
The first time I import the file into Access I create a file spec using the specs supplied by the file creator.

I save it in Access as an Import specification so I can use it to import the text file in the future.
Screenshot 2022-08-03 073255.png
We are moving away from Access, so I need a way to import the text files into Excel without having to re-invent the wheel.
I want to be able to import the text file into Excel without having to rewrite the Import Specs.

Thank you !

Michael
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to import file specs from access to excel

Post by HansV »

Access stores its import and export specifications in two hidden system tables:
MSysIMEXSpecs lists the specifications with their general settings.
MSysIMEXColumns lists the settings for each column of each specification.
In theory, you can export these two tables to Excel, but unfortunately, Excel wouldn't know what to do with them.
You might use them as documentation, but basically, you'll have to reinvent the wheel... :sad:

P.S. Why are you moving from Access to Excel? :scratch:
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

Why are you moving from Access to Excel?
I am the only person in my division that has used Access since I started in 1998.
Our IT department stopped supporting Access many years ago, and basically depend on me to handle it on my own.

Are you ready for this? :hairout:

I am retiring in about a year and they are not "backfilling" my position.

The suits figured since "everyone" knows how to use Excel, they want to convert all databases (1,000s+) by the time I leave.

Crazy.... but true story.

Michael

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to import file specs from access to excel

Post by HansV »

Ouch! :ouch: That's a fine mess they have gotten themselves into!
Best wishes,
Hans

User avatar
SpeakEasy
5StarLounger
Posts: 742
Joined: 27 Jun 2021, 10:46

Re: How to import file specs from access to excel

Post by SpeakEasy »

They'll almost certainly live to regret that!

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

SpeakEasy wrote:
08 Aug 2022, 18:53
They'll almost certainly live to regret that!
By the time they do, i will be :read: :sailing: :cheers: :doze: retired :grin:

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

Michael Abrams wrote:
03 Aug 2022, 15:01
Why are you moving from Access to Excel?
I am the only person in my division that has used Access since I started in 1998.
Our IT department stopped supporting Access many years ago, and basically depend on me to handle it on my own.

Are you ready for this? :hairout:

I am retiring in about a year and they are not "backfilling" my position.

The suits figured since "everyone" knows how to use Excel, they want to convert all databases (1,000s+) by the time I leave.

Crazy.... but true story.

Michael

Wow - 15 years later and I am still here. But I WILL retire next year, for sure :clapping:

So - is there still no way to export file layout specs from Access to Excel? (asking for a friend :evilgrin: )

Michael Abrams

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to import file specs from access to excel

Post by HansV »

The answer is still the same.
(It's 17 months since you started this thread...)
Best wishes,
Hans

snb
5StarLounger
Posts: 678
Joined: 14 Nov 2012, 16:06

Re: How to import file specs from access to excel

Post by snb »

Did you try to open these text files (csv, txt ?) plainly in Excel ? How do you assess the results ?
How many different importspecifications do you need ?
Use webqueries in Excel to store importspecifications or Use powerquery.

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

snb wrote:
13 Jan 2025, 12:33
Did you try to open these text files (csv, txt ?) plainly in Excel ? How do you assess the results ?
How many different importspecifications do you need ?
Use webqueries in Excel to store importspecifications or Use powerquery.
Yeah - I have tried to open the text file in Excel and it still asks for text file import specs.
All of the specs I have are 100+ fields Too much manual work :flee:

I'll let the suits figure this out. Thanks snb!


And HansV - I was looking at my join date. Still, 17 months is a while to wait to ask for any new possibilities :grin:

snb
5StarLounger
Posts: 678
Joined: 14 Nov 2012, 16:06

Re: How to import file specs from access to excel

Post by snb »

Yeah - I have tried to open the text file in Excel and it still asks for text file import specs.
Not if you use

Code: Select all

Sub M_snb()
  Workbooks.Open "G:\OF\adressen.csv"
End Sub
or

Code: Select all

Sub M_snb()
   sheets.add , sheets(sheets.count),, "G:\OF\adressen.csv"
End Sub
or

Code: Select all

Sub M_snb()
   Workbooks.OpenText "G:\OF\adressen.csv"
End Sub

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

I am trying to import a .txt file - not a csv file and I am not sure how I would use the code.

If I import a fixed width text file into Excel, how does Excel know what the fixed widths are? In Access, I created specs for import.

The suits will have to figure this out. So far, they haven't, so we still have Access for a bit longer :clapping:

Thank you!

Michael

snb
5StarLounger
Posts: 678
Joined: 14 Nov 2012, 16:06

Re: How to import file specs from access to excel

Post by snb »

You can test the code by replacing "G:\OF\adressen.csv" by the fullname of your file and run it.

The same applies to the following code: (the file "G:\OF\example.txt")
The library tries to figure out which structure the file has.

Code: Select all

Sub M_snb()
  With CreateObject("ADODB.Recordset")
    .Open "SELECT * FROM `example.txt`", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=G:\OF\"
    sheets(1).cells(1).copyfromrecordset .datasource
  End With
End Sub

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

Thank you - I'll take a stab at it :thumbup:

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

Assuming:
Filename: Test_BC_Import
Path: C:\Downloads
Workbook name: ImportTest_mra
place this code where?

Code: Select all

Sub M_snb()
  With CreateObject("ADODB.Recordset")
    .Open "SELECT * FROM `TestBCImport.txt`", "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\Downloads\"
    sheets(1).cells(1).copyfromrecordset .datasource
  End With
End Sub
Sorry for going on with this. I do appreciate your patience.

Michael

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to import file specs from access to excel

Post by HansV »

The code will import into the active sheet.
Shouldn't TestBCImport.txt be Test_BC_Import.txt ?
Best wishes,
Hans

snb
5StarLounger
Posts: 678
Joined: 14 Nov 2012, 16:06

Re: How to import file specs from access to excel

Post by snb »

Open the attachment to this post.
Open VBA: Alt-F11
Put the cursor in the code: eithe in sheet1 or in ThisWorkbook
Run the code F5
You do not have the required permissions to view the files attached to this post.

User avatar
Michael Abrams
4StarLounger
Posts: 597
Joined: 10 Feb 2010, 17:32

Re: How to import file specs from access to excel

Post by Michael Abrams »

OK - this is promising. You're awesome ! My company has disabled the macros, so I am going to recreate the macros in my own workbook. Do I need to save it as xlsb - what about xlsm - what about xlsx ?

And you're correct about the Test_BC_Import.txt

I will get on this in the morning.

Michael

Thank you SO much again.

Michael