Solved Import from excel to access and export from Access to Excel

[mansied]

Hi
I am trying to import an Excel sheet to access table and i find the comments section being truncated.I changed the data type in table to long text ( Rich text) .but it truncates less than 255 char.
My DATA table has two fileds with longtext( Rich Text) datatype. can you tell me which part of my codes is wrong ?
how can keep my long text as is .

Public Function Import()
DoCmd.SetWarnings False
Dim td As TableDef
Dim db As DAO.Database
Dim Name As String

'FileDialog
Set f = Application.FileDialog(3) 'msoFileDialogFilePicker
With f

   .InitialFileName = Application.CurrentProject.Path & "\"
   .Show
   Dim varFile As Variant
 
   For Each varFile In .SelectedItems
   Name = varFile
 
 
   FileName = Dir(varFile)
 
 
    Workbooks.Open(varFile).Sheets("Report Details").Activate
   Dim i As Integer
 
   i = Cells(Rows.Count, 1).End(xlUp).Row
   ActiveWorkbook.Close False
   i = i - 1
   DoCmd.RunSQL ("INSERT INTO ImportedRows (Rows) VALUES ('" & i & "');")
 
    Debug.Print i
 
   Dim oFSO                  As Object
 
//create a copy of the excel file
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Call oFSO.CopyFile(Name, Application.CurrentProject.Path & "\NewExcel\", True)
    FSO_FileCopy = True
 
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA", varFile, True, "Report Details!"       

   Next
End With

Exit Function

Error_:
If (Err.Number = 50290) Then Resume

Resume
End Function

 

[CJ_London]

Please make use of the code tags to preserve indentation, makes it much easier to read. I've done this for you on this occasion, please do so in the future - although looks like you don't bother much with indenting

Seems to be a few things which don't make sense to me

the line '//create a copy of the excel file' will error
you have an error handler, but don't appear to direct errors there
you have a variable Name, which is a reserved word so should be changed, you assign a value to it but never do anything else with it
you don't appear to be dimming the f variable
you have other declared variables which you do nothing with

suggest clean up your code, put option explicit at the top of your module and compile so you have cleared out there potential errors

I presume it is the transferspreadsheet line giving you problems

If so, suggest link to the excel file rather than import it, then use a query to copy the data to your table. You may also find you need to ensure one of the first few records contains a string >255 chars, otherwise access will assume it is just a normal text string - similar to the issue of columns interpreted as numbers based on the first few rows, then encountering a string

However usually better to import from a csv file as you have more control over the column data types

 

[mansied]

Thank you for your advice. I am very new to VBA.
If I link CSV to the database .it will be truncated to 255 automatically ?!!
the only way was not truncated when I import it manually and change those fields datatype to long text .how can I change it to VBA coding or importing or linking automatically by a button on the form?

 

[CJ_London]

as I said

You may also find you need to ensure one of the first few records contains a string >255 chars, otherwise access will assume it is just a normal text string - similar to the issue of columns interpreted as numbers based on the first few rows, then encountering a string

If I link CSV to the database .it will be truncated to 255 automatically ?!!

not if you initially go via the import manager and create an import spec. Part of that process enables you to set the column types, if you click on the advanced button you can save the spec there. The use the spec name as a parameter using transfertext

 

[mansied]

thanks
which Access version has this advance option. I am on the 365 version and don't have it.
I want to add this specification and call in importing Docmd...how can find it and add

 

[bastanu]

In the External Data tab of the Ribbon click the Import Text File not Excel.

 

[mansied]

In the External Data tab of the Ribbon click the Import Text File not Excel.

It doesn't let to choose *.XLSX format ..!! my import files are excel format nor CSV .

 

[bastanu]

Have you tried to link the file as suggested instead of importing then use an append query to add it to the final table?

 

[mansied]

Have you tried to link the file as suggested instead of importing then use an append query to add it to the final table?

Yes, I tried acLink but again I have the truncation to some fields. not work well.

 

[bastanu]

Can you modify the Excel file to add one "dummy" record as the first line (after the field names if present) with more than 255 characters for the two memo fields. Also try to change the format of the memo fields to Rich Text in the target table (DATA):

Arrrgh!! Access 2016 truncates data imported from Excel into Long Text field.

The goal is to import an Excel spreadsheet into Access 2016. One of the Excel columns contains cells with just under 17,000 characters. To accommodate this, we have set the corresponding field in the

answers.microsoft.com

 

[mansied]

yes i did the same

 

[CJ_London]

It doesn't let to choose *.XLSX format ..!! my import files are excel format nor CSV .

My point was you have more control with csv files. Excel is a very poor (tho' common) medium for data for import - have code to open your file and save as a csv

 

[Gasman]

Try this then, if you insist on keeping it as a workbook.

Add a column, with a formula of Len() and use the column and row as it's parameter. Copy for the whole workbook.
Then sort on that column descending.

That should give you the errant cells at the top and Access should then allow all the data.?

HTH

 

[bastanu]

@Gasman - but wouldn't that be somehow unpredictable as we are talking about 2 columns here, the longest entry in one column doesn't mean the other wil be over 255.

 

[Gasman]

No idea TBH?
The O/P mentioned a comments column, so I took that to be the errant column.?

Just trying to think of another way, if they do not want to do what has been reccommended?

Personally, when I have come here to ask for help, I have used what has been suggested to me.? I can't remember it never working?

 

[Isaac]

From my experience, including a very recent experience, TransferSpreadsheet will - sometimes, but not all the time - perform this unwanted cutoff.

One way to get around it is to ensure that the first line of data being imported has > 255 characters. This "wakes" Access up to import the whole thing properly. At least, this hack has worked for me.

Manipulate your file to-be-imported, such that, the first line of text contains > 255 characters on every column needed. You can delete this row after import.

 

[mansied]

I found the issue
in this line

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "DATA", varFile, True, "Report Details!"

I changed the excel type to

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "DATA1", varFile, True, "Report Details!"
and the import format is the same as excel that I want it.
Thanks all