End of File Character Issue

[ggji90]

Hi everyone,
I have a excel file in which there is an "End of File" character in the last row.
I am trying to import this excel file in to my sql server table but it gives error until I manually open file and removes the character.

What I want is to automate the process of downloading the file from source and deleting or removing the last row and import the file in table of SQL.

I am only stuck at deletion of this character, rest steps I have already automated.

Can anyone please guide me how can I do it? I tried removing via batch file and using macros but unsuccessful so far.

The file can be downloaded from here:
https://www.treasury.gov/ofac/downloads/sdn.pip

 

[BSALV]

what do you do ?
is it a part of a macro ?
EOF is (i think) ASCII 26.
Normally you 'll split on vblf or vb cbcrlf.


EDIT : Indeed in your link the last character is ASCII-26.
So while importing in excel, delete that character in the whole text.

 

[ggji90]

what do you do ?
is it a part of a macro ?
EOF is (i think) ASCII 26.
Normally you 'll split on vblf or vb cbcrlf.


EDIT : Indeed in your link the last character is ASCII-26.
So while importing in excel, delete that character in the whole text.

As I mentioned: I am trying to import this excel file in to my sql server table but it gives error until I manually open file and removes the character.

Kindly read my post again.
I want to remove this character using a batch file script.

 

[BSALV]

Kindly read my post again.
I want to remove this character using a batch file script.

this is still an excel-forum, so why adding a batch-file AND a macro

I'm not sure if you have to delete only that ch(26) or also the preceding chr(34)
So give it a try (make a copy of your original, runs it in both options and choose the best.

in this line, you can give that file another name
Set objFile = objFSO.OpenTextFile(MyFile, 2)

Sub Ascii26()

     MyFile = ThisWorkbook.Path & "\sdn.pip"                    'while downloading here it got an extra extension TXT

     Set objFSO = CreateObject("Scripting.FileSystemObject")
     Set objFile = objFSO.OpenTextFile(MyFile, 1)
     strText = objFile.ReadAll
     objFile.Close

     strNewText = Replace(strText, Chr(26), "")                 'option 1
     'strNewText = Replace(strText, Chr(34) & Chr(26), "")'option 2

     Set objFile = objFSO.OpenTextFile(MyFile, 2)
     objFile.WriteLine strNewText
     objFile.Close
End Sub