Want to format data

  • Thread starter Thread starter Sammy
  • Start date Start date
S

Sammy

I have data in Excel that is in 2 columns as below

Coloum 1 Coloum2
000199 CAON00000013
000200 CAON00000011


I wanted to create my SQL script from these columns as below

Update apdoc set user1 = 'Coloum2 value' where refnbr = 'Colum1 value'

As I have thousands of lines I have tried concatenate etc but it does not
come out ok as the ' don't come out ok and then leading 000 disappear in the
concatenate.

I would really appreciate some help on this.

Thanks

Sam
 
Is this an Excel question or an SQL question? Your post says you have
the data in Excel and that SQL is dropping the lead apostrophe and
leading zeros. I know nothing about SQL, but can you declare the
variable in SQL as text so it will read those values as text?

As a workaround, if you write a VBA script that writes the data out of
Excel and into a text file, can you import the text file into SQL? The
VBA would look like

sub WriteToTextFile()
dim OutputString as string

open "c:\abc.txt" for output as #1 'write to a file called abc.txt on
your C: drive

range("a1").select 'change this cell reference to the upper left
corner of your data

do until activecell.value = "" 'perform loop functions until encounter
a blank row
outputstring = activecell.value & activecell.offset(0,1).value
print #1, outputstring
outputstring = ""
activecell.offset(1,0).select
loop

close #1
end sub

Dave O
Eschew Obfuscation
 
Back
Top