End of File Character Issue

ggji90

New Member
Joined
Feb 24, 2022
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

1646037963237.png
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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)
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,954
Messages
6,175,600
Members
452,658
Latest member
GStorm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top