Error importing 13 digit numbers into Access2010

Question

User-8153698 posted

I have a project that I have to import a text file. comma delimited values, into an Access 2010 database...

I am experiencing a big problem that I have no idea how to work. I have two fields in the file that are 13 digit numbers... all my columns in the table are Text (255) yet this 13 digit number is causing the Data connection to close instantly with no data saved.

When I look at the data in Excel it looks like this...4.76334E+15 in Notepad 4763341000002623 (the true and correct value).

If I use Access import text tool in Append mode the table it crashes.  If I use the same tool but this time tell it to create a NEW table it works.

Error Msg...

Microsoft Access was inanble to append all the data to the table.

The contents of fields in 0 records were deleted, and 0 records were lost due to key violations.

*If data was deleted, the sata you pasted or imported doesn't amtch the field data types or the fieldSize property in the destination table.

*If records were lost, either the records pasted contain primery key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables.

Do you want to proceed anyway?

Why is Access crashing and what can I do to resolve the problem?

 

Answer 1

User-1598917946 posted

Access do not recognize long integer try storing the values as double and i am syre it will work

Answer 2

User-8153698 posted

Thanks...

What I am getting is the rows inserted but with NO VALUES in any of the fields, except the the 2 columns the table is populating Append_Date (Date()) and APpend_Time(Time())..

I was using Text 255 for a column type I changed it to Number(Double)... and as say I get blank rows inserted.

Don't klnow if thios helps but here is the insert statement I am using...

INSERT INTO RAW_Customer_Data ( first_name, middle_name, last_name, date_of_birth, address_1, address_2, city, region, code, country, email_address, phone_type, phone_number, id_type, id_number, pan, expire_date, customer_id, card_program, card_status, activation_date, hold_response_code, last_update_date ) 

SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, F19, F20, F21, F22, F23 

FROM [Text;HDR=NO;DATABASE=C:\Users\Phillip J Seaman\Code\Companies\BPMA\SumaCard 2010\FTP_DL\;].[custinfo_output Processed Thu 12 09 2010.txt]

 

Now what am I missing??? 

Answer 3

User1867929564 posted

I will use Text255  datatype.
is there any primary key in table RAW_Customer_Data ?Explain