473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How to set a delimiter when importing from a text source

Hi all,

I am importing from a text file using the following statement:

SELECT * FROM
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\DirectoryPa th\;].[FileName#txt];

I want to set the delimiter of the file, for instance a semicolon, and I do not know how to do it. I have tried with the following statements, but without good results.

1. SELECT * FROM
[Text;FMT=Delimited(;);HDR=Yes;DATABASE=C:\Director yPa th\;].[FileName#txt];

2. SELECT * FROM[Text;FMT=Delimited";";HDR=Yes;DATABASE=C:\Director yPa th\;].[FileName#txt];

Any advice will be very helpful.

Thanks in advance,

Felipe.
Oct 6 '10 #1
1 6837
MMcCarthy
14,534 Expert Mod 8TB
Your only options I think are to either change the registry (which I wouldn't advise) or to create a schema.ini file in the same folder as the text file you are importing.

ERLANDSEN DATA CONSULTING The format of a text file is determined by using a schema information file. The schema information file, which is always named Schema.ini and always kept in the same folder as the text data source, provides information about the general format of the file, the column name and data type information, and a number of other data characteristics.

Example Schema.ini file for a file named filename.txt containing semicolon delimited data with column names in the first row:
[filename.txt]
Format=Delimited(;)
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI
Oct 6 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Andreas Emmert | last post by:
Hi there, I'm trying to import a csv file into my MySQL database. Unfortunately the number format for the price column is formatted in German style, i.e. XX,XX (decimals separated by comma not...
2
by: Binary Dave | last post by:
Looking for some help. Not really sure if this is VBA or Access. I'm using Access97, and using the TransferText command, and I want the text file to come into the Access table as is; no sorting....
1
by: DS | last post by:
Anyone know about importing text from notepad, using a Pocket PC into Access on a Desktop then back again? Thaks DS
7
by: nizar.jouini | last post by:
Hello. I have long text file whitch is formatted like this: nextrow4 asdf asdf
3
by: Praetorian Prefect | last post by:
Code will not work in XP. Any ideas? Public Sub GetData() ' Declare variables Dim InputDir, ImportFile As String, tblName As String, strsql As String InputDir = ("f:\") ImportFile =...
6
by: tomasio | last post by:
Dear NG, years have passed and I am still more designer than programmer. I build a new version of my website which has a few nasty bugs, especially on my startpage: Resizing text brakes the...
4
windows_mss
by: windows_mss | last post by:
When I Select Source & Destination Dynamically, Path Getting Scatter Across The Map... hi, i can able to get the Correct Route and Path for the corresponding Source and destination, like...
3
by: gulllet | last post by:
I try to import a tab separated text file into sql server 2005 using the import guide. But when running the job I get the error message Error 0xc02020c5: Data Flow Task: Data conversion failed...
1
by: Kosmos | last post by:
Hey guys, I'm fairly familiar with access by now, but I've never worked with importing text into an access database. I have some fairly large text files (lets say, for example, a folder of 20 text...
1
by: aconti74 | last post by:
Hello I am new to vba programming/coding. I am writing a program that goes through a directory of text files and imports them into the database. The problem is eventually the database gets to big...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.