How to set a field to accept like 1/2" (1 Viewer)

m2boost

New member
Local time
Today, 15:34
Joined
Nov 7, 2019
Messages
8
I'm try to import a file into a table the is formatted using "" like I have one item description that will use a text as BACK "RAF" TWO the field on the table is setup as short text and when I run the script I get one error.

This is using a vba script that will import a csv file into the table.

Any ideas?

Thanks in advance!!!!
 

moke123

AWF VIP
Local time
Today, 15:34
Joined
Jan 11, 2013
Messages
3,852
It would help to know the error code or description, and to see the code your using.
 

m2boost

New member
Local time
Today, 15:34
Joined
Nov 7, 2019
Messages
8
It will check a table for ImportErros and the record error say Unparsable Record
 

m2boost

New member
Local time
Today, 15:34
Joined
Nov 7, 2019
Messages
8
The VBA script I'm running is below

Code:
Private Sub ImportButton_Click()
'Option Compare Database
'Option Explicit

'Function DoImport()

MsgBox "This process will take a longer time - Please wait for another message to display", _
           vbExclamation, _
           "Attention! Attention!! Attention!!!"
           
Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in CSV worksheet
' has field names
blnHasFieldNames = True

' Path to the folder that contains the CSV files
strPath = "P:\Lenoir Projects\Fiber\Labels_DB\NestedData\"

' Tablename where the data are to be imported
strTable = "tlbStickersData"

strFile = Dir(strPath & "*.csv")


Do While Len(strFile) > 0
      strTable = Left(strFile, Len(strFile) - 4)
      strPathFile = strPath & strFile
      DoCmd.TransferText acImportDelim, , "tlbStickersData", strPath & strFile, True


' Uncomment out the next code step if you want to delete the
' CSV files after been imported
'Kill strPathFile
    
          strFile = Dir()

Loop
 
 Dim FSO As Object
    Dim SourceFileName As String, DestinFileName As String

    Set FSO = CreateObject("Scripting.Filesystemobject")
    SourceFileName = "P:\Lenoir Projects\Fiber\Labels_DB\NestedData\*.csv"
    DestinFileName = "P:\Lenoir Projects\Fiber\Labels_DB\NestedData\Imported\"

    FSO.MoveFile Source:=SourceFileName, Destination:=DestinFileName

   MsgBox "Files Successfully Imported", _
           vbInformation, _
           "Congrats! Congrats!! Congrats!!!"

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:34
Joined
Oct 29, 2018
Messages
21,358
So, again, what was the error message you're getting? Also, which line is causing it?
 

m2boost

New member
Local time
Today, 15:34
Joined
Nov 7, 2019
Messages
8
So, again, what was the error message you're getting? Also, which line is causing it?

they all get imported and no issues. The problem is if field on the table that have "" on the data, that will will not import properly, basically nothing import on that line after the ".
Code:
PatID	WorkOrder	Nest Qty	Bd Feet	Sales Order#	Due Date	Item #	Item Description	Ship To Num	Ship To Desc
A	W1234	1	7.303	4276807	11/18/2019	777777	2205/2317 END BK LF" NONE	005504	INDUSTRY

The item description is 2205/2317 END BK LF" 18024N and after the " it will not finish importing the line.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:34
Joined
Oct 29, 2018
Messages
21,358
they all get imported and no issues. The problem is if field on the table that have "" on the data, that will will not import properly, basically nothing import on that line after the ".
Code:
PatID    WorkOrder    Nest Qty    Bd Feet    Sales Order#    Due Date    Item #    Item Description    Ship To Num    Ship To Desc
A    W1234    1    7.303    4276807    11/18/2019    777777    2205/2317 END BK LF" NONE    005504    INDUSTRY
The item description is 2205/2317 END BK LF" 18024N and after the " it will not finish importing the line.
So, you're saying the code doesn't error out but simply does not import all the data, but the code completes running all the way to the end. Right?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:34
Joined
Jan 20, 2009
Messages
12,849
The import specification designates a character to be used around values that contain the value delimiter (such as a comma) in csv.

The double quote is designated in this role by default but can be changed when the import specification is created.
 

sxschech

Registered User.
Local time
Today, 12:34
Joined
Mar 2, 2010
Messages
791
This may or may not work for your situation, depending on whether these are smart quotes. If you are using an import spec, try changing to UTF-8.
Code:
'After still having issues with smart quote imports from
'csv file, this site suggested to change Import
'Specification.  Edit the Code Page:
'OEM United States --> Unicode (UTF-8)
'This preserves the smartquotes “ rather than changing them
'to a combination of chars such as "GÇ£".  
'https://social.msdn.microsoft.com/Forums/office/en-US/7adeeeae-7b38-4133-bcde-419f149f9331/import-utf8-csv-file-into-access-programmatically?forum=accessdev
'20181231

I attached screen shots of import spec to show which item to change.
 

Attachments

  • CodePageBefore.PNG
    CodePageBefore.PNG
    9 KB · Views: 92
  • CodePageAfter.PNG
    CodePageAfter.PNG
    8.4 KB · Views: 87

Users who are viewing this thread

Top Bottom