Import Delimited Data Specifications

Feb 20, 2007

Hi,

I am attempting to import a .csv file into Microsoft Access. The .csv file is a raw data export from an online application used within the business.

One of the fields reported on within the file is unformatted, ie. "free-text". Some users are entering a comma within this "free-text" field, which is throwing my import table out of alignment.

Is there some way that I can continue to import the .csv file, but ignore any commas that appear within this one field.

Any advice would be greatly appreciated.

Thank you.

View Replies


ADVERTISEMENT

Using VBA Code To Import Tab Delimited Text Data

Oct 30, 2006

I have a table by name "newtab" and I was trying to import a tab delimited text file "newdata.txt" into newtab. The first line in the text file are the column names: SSN, Lastname, FirstName (all tab delimited though). The same field names exist in the destination file. However I am getting the error which says the "the field name SSN Lastname FirstName does not exist in the destination file" What could possibly be the problem? Since the field names are not separated in the error message, could it be that it is seeing all 3 field names as one and therefore cannot match them to the destination fields? Does that mean TAB cannot be used as the delimiter? Using the interactive IMPORT from access directly for the same files work really good though. However, I would like to do this programmatically since the files would be coming in weekly for me to load and they are many such files. The command I used is as below. Please I need help.

DoCmd.TransferText cImportDelim, , "newtab", "c:
eportsewdata.txt", True

View 1 Replies View Related

Import Delimited Non ASCII

Nov 4, 2005

Here is the snippet from this company that explains how this file is delimited.


Each field is delimited by character 127, DEL. Try typing this by holding the alt key, then pressing 0,1,2,7 in sequence. Try representing it with your favourite programming language with "x7F".


Using Access I want to run the import wizard but am unable to specify this delimeter. Any ideas would be great. Thanks.

View 5 Replies View Related

Import Delimited CSV File With Over 255 Fields

Mar 4, 2008

Let me preface this question with... I DID NOT CREATE NOR DO I HAVE ANY CONTROL OVER THE FILE I'M NEEDING TO IMPORT INTO ACCESS.

I've got a situation where I'm needing to normalize a delimited .CSV file on a routine basis. The .CSV file has 369 fields. When normalized correctly, the true data should only be about 60 fields.

I didn't think this would be such a hard thing... just import the first 255 fields into one table, and the remaining fields into another table. Then, using a query... normalize the database as necessary.

I've scoured this topic all over... I've seen solutions for "fixed width" files, but not delimited. The only helpful thread I've found says that this is possible only through very complicated parsing through the file.

That's where I'm stuck... This is definitely over my head. If anyone has any help on this I sure would apprecaite it.

View 4 Replies View Related

Export The Export/import Specifications For Text Files

May 10, 2007

Hello,
Does anyone knows how can I export the export/import specifications (which file/directory) and how can I import/export the specifications between different versions of Access. Thanks!

View 3 Replies View Related

Delimited Date/Time Fields Being Deleted On Import?

May 21, 2007

For anyone that might be able to help me out, I'd very much appreciate it, as this is now number 2 stupid workaround that I'd like to resolve before I need counselling...;)

I have a Date/Time field that I'm importing into my database via .csv files. The field is setup like the example below in every .csv file:

4/2/2007 8:30:00 AM

Access keeps throwing errors and deleting all the field values in this field whenever I try to import. I know that Access does this when a Date/Time field includes data that is not delimited, but these field values seem to be perfectly formatted to me... what can I do to stop Access from chucking these on import?

Right now I'm just importing the data into a "text" field, and then changing it to "date/time" afterwards, but I'm worried that once the table gets too long I won't be able to re-index all those records anymore and my database will be useless since it depends on that field being "date/time" format...

All those better than I feel free to show me how and where I've been stupid:D

View 6 Replies View Related

General :: Import Non-delimited Text File Into Access

Apr 7, 2015

I am trying to import a non-delimited text file into access, but where there is a strict hierarchy to the records, i.e.

NAME:

AGE:

DOB:

etc. etc.

The field names are constant throughout the document but the pages are of variable length depending on what is in the fields.

View 1 Replies View Related

Modules & VBA :: Import Pipe Delimited Text File Into Table?

Apr 2, 2015

I'm trying to import a pipe delimited text file into a table. I can import the entire table using the following code, but I only get one column of data (the entire data set in one column). If possible I would like to import with the columns defined or if not possible use some code for a function similar to text to columns.

Code:

DoCmd.TransferText acImportDelim, , "tblTest", "C:Work2015PPVMasterData.txt"

View 4 Replies View Related

Modules & VBA :: Attempting To Import Tab Delimited Text File With 274 Columns Into 2 Access Tables

Aug 2, 2013

I'm trying to import a text file with 273 fields into two tables. I've been able to do this with the code I found on an old thread and I'm now trying to accomplish everything with one step. The file I'm importing is tab delimited text file. With this current code I'm only able to populate the first record in the table and then I get error message. (Run-time error '3265') (Item cannot be found in the collection corresponding to the reqested name or ordinal).

Code:

Public Sub ImportTextFile()
' to use the ADODB.Recordset, be sure you have a reference set to ADO
Dim rst As ADODb.Recordset
Dim rst2 As ADODb.Recordset
Dim strFile As String
Dim strInput As String
Dim varSplit As Variant
Dim intCount As Integer

[code]...

View 4 Replies View Related

Export Specifications

Sep 27, 2005

This may be a real dumb question but is there any way to view/edit an existing export specification for the transfer text macro? I'm reviewing someone else's database and can't figure out how to view it.

View 1 Replies View Related

File Specifications And Datastructure

Dec 9, 2007

Hi,

Someone asked me to create a databasestructure and denote the corresponding file specifications so that the file can be exported to another program. I actually have to confess that I do not have any idea what is asked from me. I guess I have to design a database with tables linked by relationships to create the databasestructure. But can anybody tell me what is meant by a file specification? The only thing which I can image what is meant are te data types (numbers, text and so on)...but I guess that is not correct...

Can anybody help me please?

View 1 Replies View Related

Tables :: Export Table With Specifications

May 22, 2015

I created a table with an older version of MS Access which was replaced with Access 2010. I was able to export the table simply by selecting specifications that I created.

How do I export with these same specifications in Access 2010 ?

View 4 Replies View Related

General :: Export Specifications For Fixed Width Text File

Oct 15, 2014

I am trying to export a table (and possibly later a query) into a fixed width text file and I am not finding where to set the specifications. When I select Text File in the export tab, I only get the check box about Export datq with formatting and layout. Is that basically it? I do have the table set up with the correct widths for the fields.

An additional question:

This is a large table with 99 fields for a total width of 804 characters. Is there any limit for the six of a record for such an export?

View 3 Replies View Related

Setting Data Import To Overwrite Data On An Existing Table

Aug 1, 2007

I have a database that I import data from an excel spreadsheet into multiple times daily. The table that this data is imported into has several key fields that if the data already exisits in the table, and I attempt to import data that is the same except for one or more of the key fields is different. At this time the database it creates a different record. I am trying to get the database to overwrite the data in the database.

View 1 Replies View Related

Import Data From Excel Monthly - Not Manual Input All Data?

Jan 5, 2015

I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.

We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.

My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.

View 3 Replies View Related

How To Replace Old Data With Import Excel Data?

Apr 15, 2007

Hi, i am a new comer. i have a table for students' comment for the first semester. however, in the 2nd semester i want to replace them with import data from excel to this table, but on the same student there are 2 rows of records (1 is 1st semester and 1 is 2nd semester).

how can i replace the new data to the old data? or how can i delete all the old data in the exsiting table so that i can import a new excel file?

thx

View 1 Replies View Related

Import Data

Mar 9, 2005

Good Afternoon

I'm trying to import some data from an excel sheet to a table in my database. The import wizard won't allow me to import the data into an existing table, only into a new one which is a nuisance. The data formats are the same. Any idea's what might me wrong Please.

Thanks

VC

View 3 Replies View Related

Import Data Help

Feb 21, 2006

I'm importing data from a spreadsheet to a new table in access. After the import, there are 10 empty records added at the end of the table. I think this has happened before but I don't remember why. Any ideas? Thanks!:confused:

View 1 Replies View Related

Import Data From Csv

Sep 14, 2006

I want to import data into a table from a csv file. The table is failry lengthy but to try this out I have the following:

tblTest - the table I want the data to end up in
tblTestcsv - the intermediate table I empty then put the csv data into
test.csv - the csv file (obviously)

I have used somde code which gets the data into the tblTestcsv OK but then doesn't move it into the tblTest - I get an error each time.

Dim sSql As String

DoCmd.TransferText acImportDelim, , "tblTestcsv", sFileLocation, False

sSql = "INSERT INTO tblTest (forename,surname,phone) " & _
"SELECT F2,F3,F4" & _
"FROM tblTestcsv"

CurrentDb.Execute sSql

Any help as to waht I am doing wrong would be gratefully received.

View 3 Replies View Related

Import Data From Another DB

Feb 17, 2008

so i need to keep 2 DBs with the same forms (But different data) for the purpose of single/double data entry. i have to constantly import forms from DB1 to DB2 because of the form changes, how do i keep the existing data and import it?

when i try to use the import wizard, it always imports the tables as duplicates. so here is the scenario if it helps to explain better:

1: import FORMS/TABLE structure only from DB1 to DB2
2: DB2 is EMPTY, no DATA, just forms/table copied from DB1
3: enter some data into DB2
4: DB1 forms is updated, so I have to re-import the forms to DB2
5: new DB2 is again EMPTY. how do I re-import JUST the DATA portion from previous DB2 version into new DB2?

hope this makes sense. please provide a step-by-step if possible. like what fields to check and how to use the import wizard. thanks very much!

View 2 Replies View Related

Help Help Help???Import External Data???

Sep 12, 2005

Hi
Im having a real problem. Im trying to import a query into Excel so a fancy graph can be made.
In Excel when I go to Data-> Import External Data then connect to my Database. A list of queries and tables appear however the query I want is not in the list but it definately exists in Access why is this??
Really Frustrating

Cheers :confused: :confused: :confused:
bikeboardsurf

View 3 Replies View Related

Data Import Challenge

Jan 20, 2006

I created a database to assist in analyzing the contents of an Excel spreadsheet (provided by a third-party contract). The format of the data in the spreadsheet is denormalized to look something like the following:

Parent1ID Parent1Name Child1ID Child1Name
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name

Parent2ID Parent2Name Child1ID Child1Name
Parent2ID Parent2Name Child4ID Child4Name

It's important to note that the ChildIDs are NOT unique.

This was not a problem initially because I was able to import the entire worksheet into a single table and then run queries to populate the Parent and Child tables.

HOWEVER, they are now introducing "versioning" in a way that is causing a problem for me. When a new version is being created, they repeat the Parent and ALL of the Child rows, but only put the versioning information on the FIRST Child in the group. (They refuse to repeat the versioning information on all of the rows). So the data now looks like the following:

Parent1ID Parent1Name Child1ID Child1Name EffectiveDate1
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name

Parent1ID Parent1Name Child1ID Child1Name EffectiveDate2
Parent1ID Parent1Name Child2ID Child2Name
Parent1ID Parent1Name Child3ID Child3Name
Parent1ID Parent1Name Child5ID Child5Name

Parent2ID Parent2Name Child1ID Child1Name EffectiveDate1
Parent2ID Parent2Name Child4ID Child4Name

I MUST relate all of the Child rows for a particular version to the correct version of the Parent, but have not been able to come up with any idea of how to do so.

Does anyone have suggestions on how to approach this problem?

View 6 Replies View Related

Import Received Data

Aug 16, 2007

I need some advice on what is the best way to import data. I have a access 2003 database that tracks converter boxes that my company sends out for repair. Currently they have a excel file that is imported into the tables which establishes the converters that are out being fixed. When they come back from service they enter the date manually from the packing slip by searching for the converter in the forms search. We now are able to get the received converters on a excel spreadsheet. Is there a way to import the date from the excel file to the access table. Any advice would be great.

Gregg

View 2 Replies View Related

Import Same Data From 2 Tables Into One.

Feb 1, 2007

I have 2 tables that contain the exact same columns.
Basically titles and singers of CD's I have
I want to make one table out of them.

Is there a way to do this?

View 3 Replies View Related

Automatic Import Of Data

Oct 27, 2004

Hi there all,
I've been using sql for a while now alongside MS Enterpise Manager but have recently been given a few tasks to carry out using access. The time i've got to do my work is quite tight so if you could point me in the direction of some of some good on-line tutorials I would be most appreciative.

However the real reason i'm posting is to ask if there is any way you can automate the importing of data from a text file. I basically need this text file loaded into an access database on a weekly basis, is this possible without user interaction? I fear not.

Cheers for any help

Pete

View 1 Replies View Related

Import All Data Tables

Nov 24, 2006

Hi all

How do i import all data tables from another database using code?

Thanks

Damon

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved