Importing a note pad file

[nick243]

I am having an issue with importing a notepad file. When I go to give the file are normal specifications and run the import process. The import process goes then stops responding halfway through the process. I have looked at the sample data and everything seems to be correct. Am I missing something? All of the other files that go through this process every day work just fine. I can't find any real huge differences. I can post some things if needed.

Thanks Nick

[ranman256]

the notepad must be either delimited with commas, or some delimiter
or
all data set in fixed width columns.

then set the import definition using the text import schema.
otherwise it wont work.

[nick243]

The wizard tells me that the notepad file is in comma delimited form.

Here is a sample line

626203,38,000,12/09/16,Avenue main t, $55.00,42, $55.00,TAN/NEUTRAL STR,3/4 SLEEVE V-NE,349,79121,2866,7,29,141,38105252,8,000259

Thanks
Nick

[ssanfu]

The single line of data imported successfully for me.
Can you post the text file?

[nick243]

It is too long to post, Here is another sample. I will try to post a screen shot of my specifications.

625388,09,000,04/14/17,Avenue direct, $70.00,26, $70.00,BLACK ,BLK MESH HIGH-N,332,76667,1806,9,01,220,09258141,3,000088
625388,ZZ,04/14/17,093321806,09258141,22 ,BLACK ,BLK MESH HIGH-NECK ,000088
625388,09,000,04/14/17,Avenue direct, $70.00,26, $70.00,BLACK ,BLK MESH HIGH-N,332,76667,1806,9,01,240,09258154,0,000088
625388,ZZ,04/14/17,093321806,09258154,24 ,BLACK ,BLK MESH HIGH-NECK ,000088
625388,09,000,04/14/17,Avenue direct, $70.00,26, $70.00,BLACK ,BLK MESH HIGH-N,332,76667,1806,9,01,260,09258167,7,000087
625388,ZZ,04/14/17,093321806,09258167,26 ,BLACK ,BLK MESH HIGH-NECK ,000087
625397,09,000,04/14/17,Avenue direct, $70.00,29, $70.00,BLACK ,BLK MESH HIGH-N,332,76667,1806,9,01,280,09258170,4,000061

[ssanfu]

You could Zip the text file and attach it.......... I'm just saying......

625388,09,000,04/14/17,Avenue direct, $70.00,26, $70.00,BLACK ,BLK MESH HIGH-N,332,76667,1806,9,01,220,09258141,3,000088
625388,ZZ,04/14/17,093321806,09258141,22 ,BLACK ,BLK MESH HIGH-NECK ,000088

It looks like you have 2 different lines or are they wrapped in your post??

[nick243]

Ok, here is the text file.

They are all separate lines of orders.

[nick243]

I have looked at previous documents that have run successfully and can't seem to find any differences from the ones that worked.

[nick243]

Here are my screen shots of my specifications.

[aytee111]

The file changes at record number 258 - field1=512591. Not sure if this is your problem - but it is some kind of a problem!

[ssanfu]

I see a couple of things that would cause the import to fail:

1) The file structure changes at line 258. Up through line 257, there are 19 fields. Line 258 only has 9 fields. The import cannot handle field number changes.

2) The field types are mostly wrong (IMO). Most of the Long Integer field types should be text. For instance, the 2nd field is Department. The type is Long Integer. Then at line 258, the 2nd field is "ZZ", a string. But the field type is Long Integer. You cannot put a string in a Long Integer field.

3) A lot of the other fields I would think should be text type fields.


You could write VBA code to do the import that would handle the number of field changes (19 down to 9) for the different lines..

[nick243]

Here is the code we use

Function Importfiles()


Dim DDate, strfile As String
Dim LMsg As String
Dim DDDate As Date


'** Step 1 -- Define where and what naming sequence the files are.
ChDir ("K:\Departments\Support Services\#System-Output\")
strfile = Dir("K:\Departments\Support Services\#System-Output\SPO152TICREQ*.TXT")




    Do While Len(strfile) > 0
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "ClearInitial"
        
        DoCmd.TransferText acImportDelim, "SPO152TICREQImport Specification", "SPOTicImport", "K:\Departments\Support Services\#System-Output\" & strfile, False
        FileCopy "K:\Departments\Support Services\#System-Output\" & strfile, "K:\Departments\Support Services\#System-Output\History\" & strfile
        Kill "K:\Departments\Support Services\#System-Output\" & strfile
        
        DoCmd.OpenQuery "CaptureTicReqs"
        DoCmd.OpenQuery "Capture_PO_Color"
        DoCmd.OpenQuery "PO_CountOf_Color"
        DoCmd.OpenQuery "Update_CountOfColors"
        strfile = Dir
    Loop
DoCmd.SetWarnings False
DoCmd.OpenQuery "UpdateVendorName"
Forms![MainFrm]![MainSub].Requery
DoCmd.SetWarnings True
'Call VerifyImportErrorTables


End Function

[nick243]

The system seems to be able to handle errors but I am not sure how that is accomplished.

[nick243]

Ok, I do see the the VerifyImportErrorTables call at the bottom but when I go into the object browser and search for that code it does not appear in the library.

[ssanfu]

Ok, I do see the the VerifyImportErrorTables call

That call is commented out, so it doesn't do anything.

I see things in your code that I would change, but the problem is not in the code. It is in the text file.
Why did the data change? The format of the text file MUST be consistent for the TransferText command to execute properly (without errors). Otherwise you would have to have code that reads each line of the text file and processes the line.


So
1) edit the text file(s) to delete the lines that have "ZZ" in the "Department" column (a lot of work) or
2) find out why the lines with "ZZ" in the "Department" column were added and have the text file(s) recreated, without the extra lines. (my choice)
3) rewrite the import function to read each line of a text file and import/process the lines that meet the requirements.