appending data from text file with different column orderings

I'm interested in appending data to an Access table without having tobe constrained by the order of the columns in an external file.  Forinstance, if I have a table with 4 fields - A, B, C, D - I would liketo be able to have my external file in any column order, say D, C, B,A.  Is this possible without programming?>From what I've seen, it seems that I would have to have my externalfile ordered the same as the table - A, B, C, D - and I'd like to haveindependence of the table ordering.Denis
0
sysdrk
3/14/2007 7:39:20 PM
access 16762 articles. 3 followers. Follow

2 Replies
848 Views

Similar Articles

[PageSpeed] 46

On Mar 14, 4:37 pm, John Nurick <j.mapSoN.nur...@dial.pipex.com>wrote:> Hi Denis,>> 1) If the external file is a delimited text file whose first row> consists of column headers that match the field names in the Access> table, the order of the columns doesn't matter.OK, that's what I hoped the answer would be.  However when I did someinitial googling on this topic I found a microsoft websitehttp://office.microsoft.com/en-us/access/HA010546511033.aspxon this topic that says:Note  If you want to append data to an existing table, your text fileshould not have column headings- they will interfere with the importprocess. Confirm that the columns of data in your text file will workwith the data types of the fields in your Access table. For example,to successfully import a date into a Date/Time field in an Accesstable, the field in the text file should be in a format that Accesswill recognize as a date. It may take a little experimentation on yourpart to get this right in your text file.This quote is in reference to using the Import Text Wizard.  Do I usesomething other than the Import Text Wizard to accomplish this task?Or is this reference wrong?Denis
0
sysdrk
3/15/2007 2:16:49 AM
In my experience case (1) does work - provided that file and table havethe same number of columns, the data types are compatible and the columnnames are identical. I.e. the only difference is that the columns are indifferent orders. I have suggested a correction to the article you cite. On 14 Mar 2007 19:16:49 -0700, sysdrk@wowway.com wrote:>On Mar 14, 4:37 pm, John Nurick <j.mapSoN.nur...@dial.pipex.com>>wrote:>> Hi Denis,>>>> 1) If the external file is a delimited text file whose first row>> consists of column headers that match the field names in the Access>> table, the order of the columns doesn't matter.>>OK, that's what I hoped the answer would be.  However when I did some>initial googling on this topic I found a microsoft website>>http://office.microsoft.com/en-us/access/HA010546511033.aspx>>on this topic that says:>>Note  If you want to append data to an existing table, your text file>should not have column headings- they will interfere with the import>process. Confirm that the columns of data in your text file will work>with the data types of the fields in your Access table. For example,>to successfully import a date into a Date/Time field in an Access>table, the field in the text file should be in a format that Access>will recognize as a date. It may take a little experimentation on your>part to get this right in your text file.>>This quote is in reference to using the Import Text Wizard.  Do I use>something other than the Import Text Wizard to accomplish this task?>Or is this reference wrong?>>Denis--John Nurick [Microsoft Access MVP]Please respond in the newsgroup and not by email.
0
John
3/15/2007 6:11:46 AM
Reply:

Similar Artilces:

Import Outlook Data
I have Outlook 2000 on two computers. I would like to merge the data from the 1st computer to the 2nd,. I DO NOT want to setup a new calendar, contact, email folders, etc. I want to add to the existing ones. I see how to import data from other programs but not from Outlook. I know it must be simple. Help please. Bob Copy the pst-file of the 2nd computer to the first and choose "Import from file and". Then set it to import a pst-file and browse to the pst-file you've just copied. -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an...

Importing Data
I have two users who have just had email accounts set up and will be accessing mail exclusively through OWA. Both would like to import existing data from their old accounts into OWA. Is this possible and, if it is, can anyone tell me how to do it? Any help would be appreciated. Thanks. "NOBI" <NOBI@discussions.microsoft.com> wrote: >I have two users who have just had email accounts set up and will be >accessing mail exclusively through OWA. Both would like to import existing >data from their old accounts into OWA. Is this possible and, if it is, can >anyone...

Help with organizing data
I was given data in the following format: 00 10 20 30 40 50 0 25 30 41 43 36 38 1 40 56 52 38 35 20 2 25 19 38 44 46 50 3 52 58 48 56 50 48 Is there an easy way to convert it to the following format: 0:00 25 0:10 30 0:20 41 0:30 43 0:40 36 0:50 38 1:00 40 1:10 56, etc. Any suggestions would be apprecated. Assume data is in G1 to M25 go to an open area C1? insert "0" and copy down to C6 in C7 enter =C1+1 copy down til you hav...

Batch loading Timesheets data from Excel files to EPM
Dear all, I had a request from one of our clients' regarding the batch loading of Timesheet Data from Excel files to EPM. I investigated the request and my conclusion is that this can be done using SQL Integration Services (via a DTS). My questions are the following: 1. Is it enough to batch load data on the following tables (of Published database) ? MSP_TIMESHEET_ACTUALS MSP_TIMESHEET_LINES MSP_TIMESHEETS 2. How can I batch create the UID (identifiers)? Is there a specific Stored Procedure for that? I'll appreciate any answer on that. Best regards, Kons...

Linking a Visio shape to an Excel data cell
I am more than new to Visio. I am making timelines that will show various stages of a product. I have created cylindrical timelines, and I can import my Excel data. Is there a way for me to link various cells types (Beta 1, Beta 2) to Visio shapes so that instead of a date the shape will be shown on the timeline? That is, can I configure Visio so that every Beta 1 automatically displays as a timeline square and every Beta 1 automatically displays as a timeline diamond? (And if so, how?) Thanks~ -- Billie First of all shapes are positioned horizontally on the timeline by date, so it...

Text box moves around
I have inserted a text box into an embedded chart. When the chart is activated (clicked on), the text box is in the position I want it. But as soon as the chart is de-activated (i click somewhere else in the spreadsheet), the text box repositions itself up and to the left. When I click back on the chart it moves back into the correct position, and so on. I might add that if the zoom is set at 100% then the text box stays where it should. If it is not at 100%, then the problem happens. If I zoom in and out, the text box does not resize along with the chart, and ends up in wrong pla...

Sales Order Processing #3
Can someone explain what is the difference doing an invoice individually as opposed to doing it in a batch It depends upon posting setup, mostly. You can set up GP so that when you post a batch, the GL journals get created and posted automatically. If you post an individual transaction, you will create a GL journal transaction that is unposted. Also, the posting settings can determine what date is used for the GL transaction: a batch date or transaction date. -- Charles Allen, MVP "rcr" wrote: > Can someone explain what is the difference doing an invoice individuall...

Print data in form field lists
Using Word 2007 - have a form with multiple drop down lists. Is there a way to print a copy of the form which displays all the options in the drop down lists to make updating easier? I am not sure how printing a document with all the dropdown items displayed (which is not possible) would help with 'updating'. If you want to modify the values in a dropdown field more simply - take a look at the add-in at http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <...

How do I set up a tickler file?
I need to be able to set up a tickler file where I can enter a name and discharge date then have the program automatically remind me to take an action in 2 months. I am using office pro 2003.I would like to do this in excel or outlook. Why not use Outlook's calendar function? -- Ken Russell kenrussellyourhat@optushome.com.au Remove yourhat to reply by e-mail .. "moe3rd" <moe3rd@discussions.microsoft.com> wrote in message news:BB1A0479-4242-4E05-8BFD-AAB264F948ED@microsoft.com... >I need to be able to set up a tickler file where I can enter a name and > discha...

Query Delete and append
I am trying to add information to a table with an append query and that works fine since I duplicated the table. The information comes from a linked XLS sheet, I need to delete the information and replace it every day. When I created a delete query it works fine but when I try to add the information again using the append query it does not work unless I open the query in design view, save it and run it. I have dozens of other queries doing the same and they all work fine. What am I doing wrong? I looks like I am getting a 3349 error but why does it work once and then when I delete the ...

To get client data from a saved file back to the master file
I have designed a programme that records data and also links to another programme, but when I save the client information the name of the file changes and I loose the link. I want to be able to open a master copy each time and get the client data from the saved file so I can update each time and resave updates information, this way the link will always be there, I hope? ...

How do I work with Excel 2007 and htm files?
I created a htm file in Excel 97-2003 and I was able to access my tables and change the information around. When I open the file in the new Excel 2007, it changes a lot of my numbers to dates and some other crazy numbers I have no idea where they come from. I've tried going to excel options and turning off automatic calculations but that doesn't help. How do I get excel to not change any of the information in the cells of my workbook? [In the previous excel all you had to do was insert an apostrophe ' to prevent any changes.] Example: Supposed to say: 01-05 Says: 39087 ...

File names and locations
I'm new to Outlook and have loaded it onto a new computer running Windows 7 so that I can sync my Blackberry to it. I am going to install it on to another XP machine and would like to be able to copy the files for the contacts, calendar, email folders, etc to the new machine. Can anyone tell me the file names and possible locations that I would need to move the data to another machine? Thanks, Rick Morrison Look for pst-files. You can look up your currently connected pst-files and their paths via; File-> Data File Management... For transferring details se...

Excel OLE data in PPT reverting to OLD data after macro updation
Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) ...

File SKU011
I need this file and I cannot find it on the Microsfost Office professional 2003. It contains the converter file. How can I get this Hi Bill Leonard (anonymous@discussions.microsoft.com), in the Microsoft� newsgroups you posted: || I need this file and I cannot find it on the Microsfost || Office professional 2003. It contains the converter || file. How can I get this http://support.microsoft.com/default.aspx?scid=kb;en-us;826511 -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org This posting is provided "AS IS" with no w...

Strange Files in My Documents Created by Outlook 2003
Every email that Outlook receives writes a text file in the My Documents folder. These files contain the complete email text that with all the tags such as 'received' and 'from'. Also, These files begin with the letter s and both the name and extension appear to be generated sequences. We are getting a ton of these files. It doesn't happen to all our users still we can't find the triger for this condition. Thanks in advance. John JKHarris <jkharris0@gmail.com> wrote: > Every email that Outlook receives writes a text file in the My > Documents folder...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

DATA HIDING
I WANT TO HIDE DATA IN A SINGLE CELL Format the cell with protection of Hidden, and then protect the worksheet. "REDSHARK" wrote: > I WANT TO HIDE DATA IN A SINGLE CELL ...

Chart does NOT show data entered for the values represented
I continue to find a problem in excel 2007 that was not present in 2003. I will input 2or more columns and rows of data to create a chart, then create the chart; but IF MORE THAN ONE data path is selected to be charted, the other data paths are charted incorrectly. They do not reflect the values in the cells! Obviously then, my charts are wrong and I can't rely on them for analysis. Please respond if this has happened to you and you know how to correct for this. I am very frustrated. -- Judi Hi Judi, Data Path? what is this, are your charts refering to other workbooks? Why ...

Graff data series
I have a graff for sales. Data series 1 = Budget Data series 2 = Actual I want the value that is shown with data series 2 to be a % of the value of data series 1. ...

Filling in Data
I have several points where I need data. I only have the start and the end values and I want to fill in each of the points between the two. How is that accomplished. Example. Point 1 = 50, Point 10 = 600. I need Points 2-9. 550/9 = the increment to add moving from point 2 to point 9 Jamie wrote: > I have several points where I need data. I only have the start and the end > values and I want to fill in each of the points between the two. How is that > accomplished. > > Example. Point 1 = 50, Point 10 = 600. I need Points 2-9. Hi, Try this and ente...

Help with formatting text in a textbox
Hi All, I have been trying everything I can think of, and still not getting the results I'm after. I am trying to summarize an order in a textbox, by building a string and assigning it to the caption property of the text box. I create a recordset based on what's in the table, then loop through it and build my string(s). I want to display everything nicely, so I am trying to space each field so that I can put in a header row of labels, and then have each record show nicely spaced below. I am now finding that even when I take into account the length of the data inthe field, I still ca...

Encryption while typing my text. What F keys get me back?
While typing I must hit a wrong key and suddenly my keyboard letters don't match what I'm typing - looks encrypted. I think I'm supposed to use some combination of Fkeys with either the Shift, Control, or Alt key. Help please. Make sure that the correct keyboard layout (input language) has been activated (Control Panel, Regional and Language Options). -- Stefan Blom Microsoft Word MVP "bkeen" <bkeen@discussions.microsoft.com> wrote in message news:10B5C31C-5F5D-42FB-AF78-1BD66F84DE00@microsoft.com... > While typing I must hit a wrong key a...

personal backup files
I am trying to export Outlook backup files (.pst) to my desktop so that I can then save them on CD. When I go to open them, a message says this is not a vaild WIN32 application. Am I doing something wrong in the process? Neil Dubin Don't export but simply copy your pst-file to a CD when Outlook is closed. When you want to restore it copy it back to your harddisk and remove the "Read Only" file attribute. Then connect to it by using File-> Open-> Outlook Data File. For more info see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaaij [MVP-O...

how to summerise values from specific columns
Hi, I have tried to find the right formulah, but failed. I have three column Amount A Amount B Amount C 30 10 20 34 and a fourth column where I have to fill in % that applies to all values A-C in the row. 12% 45% How to make a formula in a cell that summerise all amount A's * the respective % For example: 30 * 12% + 10 * 45% = Also for two other cells for B and C Bart Vista/excel 2007 try sumproduct() =SUMPRODUCT(A2:A3*D2:D3) HTH Regards Sebation "AA Arens" <bartvandongen@gmail.com> ??????:1189666230....