using schema.ini

[cheaster]

David,

Somehting you may not know, but it IS possible to tell Access how to import a file using an external specification. This specification is called a Schema. This "schema.ini" is this file he is refering to.

There are a lot of issues with is thing I have seen it work, so I know it works. But it is the hard and long way around, like going Amsterdam > Madrid but by way of Amsterdam > Berlin > Rome > Madrid

Internal specifications can and will do what he wants, just as soon as he (or she) can find the time to quit beeing stubburn and listen to us "pro's"
(Dont mean to insult/hurt or anything along those lines...)

Dear Namlian!

I belive you, but a can't do what you told. I don't often use Access. I don't really understand that the schema.ini can use for exporting? i really need your help, so please tell me for step to step how can i do my schema.ini and use for exporting data

thank

 

[DCrake]

Excuse me but were we not talking about Export specificiations not Import specifications. And after all that why does he not simply create a schema.ini file in notebook and distribute it with the app? Personally I would store all this information in dbProperties, wouldn't you?

 

[namliam]

OK, so you need my help/ our help, so when are you going to start READING and LISTENING to our help!!!????

OK just ONE MORE TIME, DO NOT use the Schema.ini!!!
DO use the Specifications as used in access internaly!!

AND yes you can do ALL you want with that!

Now... Open your database and take these steps (from a previous post of mine)
Import Export the file manually once:
- File > Get external data > Import Export
- Find and select the file Give the file a name and save it anywhere
(you will press cancel later thus no file is actually generated)
- Walk thru the Wizard, untill you press "Finish" and stop there.
- Click advanced
- Click Save as, give it a <name> and save.

Now use <name> instead of Schema.ini and you are good to go.

 

[cheaster]

OK, so you need my help/ our help, so when are you going to start READING and LISTENING to our help!!!????

OK just ONE MORE TIME, DO NOT use the Schema.ini!!!
DO use the Specifications as used in access internaly!!

AND yes you can do ALL you want with that!

Now... Open your database and take these steps (from a previous post of mine)
Import Export the file manually once:
- File > Get external data > Import Export
- Find and select the file Give the file a name and save it anywhere
(you will press cancel later thus no file is actually generated)
- Walk thru the Wizard, untill you press "Finish" and stop there.
- Click advanced
- Click Save as, give it a <name> and save.

Now use <name> instead of Schema.ini and you are good to go.

oh thank you and sorry for my slow uptake

 

[namliam]

Now... Open your database and take these steps (from a previous post of mine)
Export the file manually once:
- File > Export
- Give the file a name and save it anywhere
(you will press cancel later thus no file is actually generated)
- Walk thru the Wizard, untill you press "Finish" and stop there.
- Click advanced
- Click Save as, give it a <name> and save.

Now use <name> instead of Schema.ini and you are good to go.

Hmz, strikethrough doesnt work on this board see above without the extra old junk in there.

 

[ArupBanerjee]

Hi,

you can use schema.ini but, not with docmd.transfertext. You can use following code instead.

   Set cn = CreateObject("ADODB.Connection")
        Set cn = CurrentProject.Connection
        ssql = "select top 1 * into "
        ssql = ssql & "[text;database=" & OutputFolder & "\;HDR=Yes].[" & strFileName & "]"
        ssql = ssql & " from [" & TableName & "]"
        cn.Execute ssql

You can learn more about schema.ini from this article "https://officeaccelerators.wordpress.com/2015/02/08/exporting-text-file-from-access/"

 

[smig]

you can use "schema.ini" file, but it's easier to use the internal spec.

As I did not use Schema.Ini file for many years I'm not sure if acExportDelim will work with fixed width file. Try using acExportFixed instead.

If you insist use a "schema.ini" file I think this variant should be left blank. The Schema.Ini file must be on the same folder with the imported file.
This is from the help:
SpecificationName Optional Variant. A string expression that's the name of an import or export specification you've created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini

If you use AC2010 it's tricky to get into the Advanced option to create the Spec. You must export your file, at first stage giving it the CSV extension. after you created the Spec you can change to TXT for the final export stage.

 

[nevermanas]

Hi there!

Current MS Access help says "you must either specify an argument or use a schema.ini". The trickiest thing here is this – to tell Access to use the schema.ini file you must skip the SpecificationName argument. Not blank string like "" – but skip completely! Just delete ["schema.ini"] from previous example and you'll get it right:

DoCmd.TransferText acExportDelim, , "l", "c:\acc\sz.txt", True, ""

 

[Gasman]

9 year old thread?

 

[nevermanas]

9 year old thread?

I think it is still relevant for today. I met a problem how to export singe large table from MSSQL to CSV txt. And MS access was very good at this by exporting linked tables. All suggestion that I could find how to do it from MSSQL directly where just quirky workarounds, but not real CSV. The schema.ini file lets you set export specification in more flexible way than GUI wizard.