473,890 Members | 1,355 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MS Access menu import not same as TransferText

3 New Member
Hi All,

I'm attempting to automate the import of .txt files into Access.

When I use the File > Get External Data > Import function and select my import specification, the data is imported correctly and in order.

When I use the TransferText macro, and specify all the same paramenters i.e. fixed width, import specification, file path, table name etc, the data is imported; however, it is 'jumbled.' It is not in the same order as the text file (does not import the same way as when I do it manually).

It is critical that the data is imported in the correct order for it to be useful.

Has anyone ever encountered this issue before? Can someone provide some insights?

Many thanks!!!
Apr 27 '07 #1
5 3513
MMcCarthy
14,534 Recognized Expert Moderator MVP
Hi All,

I'm attempting to automate the import of .txt files into Access.

When I use the File > Get External Data > Import function and select my import specification, the data is imported correctly and in order.

When I use the TransferText macro, and specify all the same paramenters i.e. fixed width, import specification, file path, table name etc, the data is imported; however, it is 'jumbled.' It is not in the same order as the text file (does not import the same way as when I do it manually).

It is critical that the data is imported in the correct order for it to be useful.

Has anyone ever encountered this issue before? Can someone provide some insights?

Many thanks!!!
Check your specification file. There may be some problems with it.
Apr 27 '07 #2
bwlee
3 New Member
Check your specification file. There may be some problems with it.
I use the same specification file in TransferText as I do using Menu > import...
Apr 27 '07 #3
MMcCarthy
14,534 Recognized Expert Moderator MVP
I use the same specification file in TransferText as I do using Menu > import...
Check it anyway.
Apr 27 '07 #4
bwlee
3 New Member
Check it anyway.
I've been banging my head against the wall for the past 24 hrs because of this problem. This was seemingly a straightforward task. I've checked and double checked the import specification. As I mentioned before, I use the same one when I import manually as I do for the TransferText macro. Still not sure why the automation method doesn't work properly...
Apr 27 '07 #5
MMcCarthy
14,534 Recognized Expert Moderator MVP
Honestly not sure what is going on here. I've never had this problem. Usually though I use VBA to import text files. You can use code something like the following. It gives better control anyway.
Expand|Select|Wrap|Line Numbers
  1. Function ImportTextFile()
  2. Dim fhandle1 As Integer
  3. Dim fline As String
  4. Dim fPath As String
  5. Dim fld1 As Long
  6. Dim fld2 As Date
  7. Dim fld3 As String
  8. Dim strSQL As String
  9.  
  10.     fpath = "Full path of file.txt"
  11.     ' open the text file to be read
  12.     fhandle1 = FreeFile()
  13.     Open fpath For Input Access Read Lock Write As #fhandle1   
  14.  
  15.     Do While Not EOF(fhandle1) ' loop until you reach the end of the file.
  16.  
  17.         Line Input #fhandle1, fline       
  18.  
  19.         fld1 = Left(fline, 5) 
  20.         fld2 = Mid(fline, 6, 10) 
  21.         fld3 = Left(fline, 16, 30) 
  22.  
  23.         strSQL = "INSERT INTO TableName (Field1, Field2, Field3) " & _
  24.            "VALUES(" & fld1 & ", #" & fld2 "#, '" & fld3 & "')"
  25.         DoCmd.RunSQL strSQL
  26.     Loop
  27.  
  28.     Close #fhandle1
  29.  
  30. End Function
  31.  
Mary
Apr 27 '07 #6

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

Similar topics

11
3433
by: Grim Reaper | last post by:
I am importing a .csv file into Access that has 37 fields. My problem is that sometimes the last field only has data at the end of the column (it looks like when you import a file into Access, for the last field, it only checks the top few 'cells' to see if there is any data, if not, the field is not imported). How do I 'force' Access to import the field, regardless if there is data in the top of the field or not? For instance, I might...
1
2807
by: D Mat | last post by:
Hi, I'm trying to get MS Access 2000 to automatically import a series of (~200) flat text, tab delimited, data files into a single Access table, with consistent fields and rows. The files have different, but somewhat logical naming structures (ex.Jun01.txt, Jun02.txt, Jul01.txt, etc...). Is there a relatively simple way to accomplish this, considering i'm new to Access. I was especially hoping to do this without having to manually...
2
4764
by: Roald Oines | last post by:
Hi, I'm working on converting several of my Access 97 databases to Access 2002 format (the company's changing from NT 4 to XP and updating Office at the same time), and this one has me stumped. As a performance enhancer, I use VBA code (transfertext) to export the results of a query (usually 20-30 thousand rows with a few dozen fields of customer information) to the user's temp folder as a text file and then link to it using a text file...
0
1421
by: matchine | last post by:
This is a recommendation based on my research on an issue with the transfer text functionality. The comments below were from a tech I approched for help. "The transfer text process blocked after importing 6139 records each time on my side. After further research, there maybe a memory leak in TransferText. When the number of records exceeds its limitation, the transfer process blocked. Therefore, please abandon TransferText and write...
1
2202
by: hedgracer | last post by:
I have a strange problem with a docmd.transfertext statement. I have set up an import specification to transfer in a .txt file by fixed width. When I do the import using the specification the file imports just fine into the table. When I use the transfertext statement for import the contents of the file are loaded into the first column. Has anyone run into this problem and do they have any suggestions/solutions? Here is the code in...
2
9240
by: john | last post by:
I have 400 different Excel-spreadsheetfiles, same structure, all with only one record in it, and all residing in the same folder. Every now and then new Excel files are being added. In my Access file I have a form and via a button on that form I would like to periodicly automatically import all of those (new) Excelfiles in an Access table with an identical structure. What's the easiest way of making such an automated import? Is this doable?...
5
7567
by: Alice | last post by:
I'm trying to upload all .txt files from a specific folder using the following code, but it didn't work. HELP The problem lies in DoCmd.TransferText acImportFixed, "Imperial Import Specification", TableName:="1089_daily", FileName:="myFile", False, "" where myFile is defines as myFile = Dir("C:\Temp\test\*.txt") ... but error occurs Below is the entire code:
3
2226
by: derfer | last post by:
I am trying to import a .inf file into access (the file comes from the output of some 3rd party software). I have managed to convert the file by opeing it in notepad and re-saving then a seperate script to open the txt file and import. Both these scripts seem to work well (ish) but is there a better way of doing this? and is there a way of doing this and only having to run a single script? Copies of the script below. Any help would be much...
8
11109
by: Irene | last post by:
Hi, I have an MS Access Database with 1 Table containing about 2 million records in Unicode (diferent languages). I would like to export the Table to a Text file (CSV, Tab, etc.) Access dows only export about 65,000 at 1 time. Almost an imposible job.
0
9978
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9822
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10819
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10462
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9633
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8016
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7169
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6045
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4676
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.