cstraim
asked on
replace text in CSV file through VBS to import into access
Hello
I have written a VBS file to Find " in my csv file. I need to get rid of all the " as I don't need them and it's hurting the quotes being used as a text qualifier when importing the file into access as a table (unparseable record) I think i'm missing something in the code because I keep getting an error.
First here is the code
Dim myXL
Set myXL = CreateObject("excel.applic ation")
myXL.workbooks.Open "g:\Subgroup\Access Data\lbx\RAW DATA\oSPEC\tbl_atl_ospec.c sv"
myxl.displayalerts = false
myXL.activeworkbook.Sheets ("Sheet1") .Select
myXL.activeworkbook.Cells. Replace What:="""",
Replacement:="", LookAt:=xlPart
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
myxl.displayalerts = true
myXL.activeworkbook.Close False
Set myXL = Nothing
The error is "expected Statment"
Code: 800a0400
Source: Microsoft VBScript Compiliation Error
It specifically references this line and "what:=""",
myXL.activeworkbook.Cells. Replace What:="""",
Can someone look over my code in its entirety and help me make sure that I'm doing this right
I am trying to remove all " in the file.
Thanks
Cory
I have written a VBS file to Find " in my csv file. I need to get rid of all the " as I don't need them and it's hurting the quotes being used as a text qualifier when importing the file into access as a table (unparseable record) I think i'm missing something in the code because I keep getting an error.
First here is the code
Dim myXL
Set myXL = CreateObject("excel.applic
myXL.workbooks.Open "g:\Subgroup\Access Data\lbx\RAW DATA\oSPEC\tbl_atl_ospec.c
myxl.displayalerts = false
myXL.activeworkbook.Sheets
myXL.activeworkbook.Cells.
Replacement:="", LookAt:=xlPart
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
myxl.displayalerts = true
myXL.activeworkbook.Close False
Set myXL = Nothing
The error is "expected Statment"
Code: 800a0400
Source: Microsoft VBScript Compiliation Error
It specifically references this line and "what:=""",
myXL.activeworkbook.Cells.
Can someone look over my code in its entirety and help me make sure that I'm doing this right
I am trying to remove all " in the file.
Thanks
Cory
Try using 3 double quotes instead of four.
Try,
myXL.activeworkbook.Cells. Replace What:='""',
(single quotes on the outside).
The problem your having is that you need to supply the quote character within a string argument in code.
JimD.
myXL.activeworkbook.Cells.
(single quotes on the outside).
The problem your having is that you need to supply the quote character within a string argument in code.
JimD.
cstraim: confirm you are trying to get rid of single doublequotes in you csv file - or all of them?
<<cstraim: confirm you are trying to get rid of single doublequotes in you csv file - or all of them?>>
I wasn't sure of that either.
Is it all " (quote) or all "" (quote, quote) that is attempting to be removed.
JimD.
I wasn't sure of that either.
Is it all " (quote) or all "" (quote, quote) that is attempting to be removed.
JimD.
Hello cstraim
This is not quite obvious from your post, but it seems you are missing the "line continuation characters". In VB an VBS, you cannot use a new line anywhere, as you could in most other programming languages. To indicate that the logical line continues on the next physical line, you need to end it with a space and a single underscore, for example like this:
myXL.activeworkbook.Cells. Replace _
What:="""", _
Replacement:=""
Note that the remaining parameters are not needed in your case: you can use their default values.
Second problem: the activeworkbook does not have a Cells collection, only sheets do. You would neet do specify on which sheet you want to work:
myXL.activesheet.Cells.Rep lace ' etc...
Third, VB, unlike VBA, does not work with object libraries, and hence cannot use named arguments (e.g. What:=). You need to specify the needed arguments in the correct order, without names.
Finally, you close without saving, so that your changes are lost.
As a side note, it woul probably be better to replace double quotes by double single quotes, because they probably have a meaning: " --> ''
So:
Dim myXL
Set myXL = CreateObject("excel.applic ation")
myXL.workbooks.Open "g:\Subgroup\Access Data\lbx\RAW DATA\oSPEC\tbl_atl_ospec.c sv"
myXL.activeworkbook.Sheets ("Sheet1") .Cells.Rep lace """", "''"
myXL.activeworkbook.Close true
myXL.Quit
[use ...Replace """", "" to delete all double quotes, as in your sample]
Good luck!
(°v°)
This is not quite obvious from your post, but it seems you are missing the "line continuation characters". In VB an VBS, you cannot use a new line anywhere, as you could in most other programming languages. To indicate that the logical line continues on the next physical line, you need to end it with a space and a single underscore, for example like this:
myXL.activeworkbook.Cells.
What:="""", _
Replacement:=""
Note that the remaining parameters are not needed in your case: you can use their default values.
Second problem: the activeworkbook does not have a Cells collection, only sheets do. You would neet do specify on which sheet you want to work:
myXL.activesheet.Cells.Rep
Third, VB, unlike VBA, does not work with object libraries, and hence cannot use named arguments (e.g. What:=). You need to specify the needed arguments in the correct order, without names.
Finally, you close without saving, so that your changes are lost.
As a side note, it woul probably be better to replace double quotes by double single quotes, because they probably have a meaning: " --> ''
So:
Dim myXL
Set myXL = CreateObject("excel.applic
myXL.workbooks.Open "g:\Subgroup\Access Data\lbx\RAW DATA\oSPEC\tbl_atl_ospec.c
myXL.activeworkbook.Sheets
myXL.activeworkbook.Close true
myXL.Quit
[use ...Replace """", "" to delete all double quotes, as in your sample]
Good luck!
(°v°)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Interesting approach. Though, I would do it a little differently. Usually CSV files are simple text files.
If you have to use VBS you can do something like this, e.g.
Dim objFSO
Dim fsoFile
Dim strTemp
Set objFSO = CreateObject("Scripting.Fi leSystemOb ject")
Set fsoFile = objFSO.OpenTextFile("your textfile", 1) ' the "1" is the flag for reading
strTemp = fsoFile.ReadAll
fsoFile.Close
Set fsoFile = objFSO.OpenTextFile("your textfile", 2) ' the "2" is the flag for writing
fsoFile.Write Replace(strTemp, """", "") ' This does the replacement of the quotes and puts it back to the file.
fsoFile.close
If you reference Windows Script Host to your Access application, you can do this in one go, by putting the code right before your import routine. If you're sure, that the delimiter is always set correctly, you can import the textfile via FSO line-by-line directly using the Split-function to put every line into an array, and then put it to the database.
DD
If you have to use VBS you can do something like this, e.g.
Dim objFSO
Dim fsoFile
Dim strTemp
Set objFSO = CreateObject("Scripting.Fi
Set fsoFile = objFSO.OpenTextFile("your textfile", 1) ' the "1" is the flag for reading
strTemp = fsoFile.ReadAll
fsoFile.Close
Set fsoFile = objFSO.OpenTextFile("your textfile", 2) ' the "2" is the flag for writing
fsoFile.Write Replace(strTemp, """", "") ' This does the replacement of the quotes and puts it back to the file.
fsoFile.close
If you reference Windows Script Host to your Access application, you can do this in one go, by putting the code right before your import routine. If you're sure, that the delimiter is always set correctly, you can import the textfile via FSO line-by-line directly using the Split-function to put every line into an array, and then put it to the database.
DD
cstraim: Want to clarify a few things?
ASKER
WOW..thanks all..Im sorry i didnt post sooner. I was in meetings and working on a high profile project that took all of my time. I will be looking at this tomorrow. Thanks for all of your posts. I really REALLY appreciate it :)
ASKER
Thanks again to everyone. my original question was answered by the simple search and replace tool that can be called using a batch file. unfortunately many of you were totally right in the fact that I was trying to fix the unparseable record error in Access. I have no idea how to do that programmatically..Ill have to open a new question.
I'm sorry i took so long to respond. I had to put this piece of my project on hold, because of some immediate fires that needed to be put out
Thanks again!!!
I'm sorry i took so long to respond. I had to put this piece of my project on hold, because of some immediate fires that needed to be put out
Thanks again!!!
Hmm. As I stated, I did not provide a VB solution. The others did (although feedback on success or failure of method was not forthcoming.
Can you give a logic for your accept?
Can you give a logic for your accept?
I did not refresh, so I did not see your post just above.