Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Want to format data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Want to format data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
change the display data format cell already contain data as date | Excel Discussion (Misc queries) | |||
Increase Your Business By Data Conversion, Data Format and Data EntryServices in India | Excel Worksheet Functions | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
External Data Queries - Data Range Properties v Spreadsheet Format | Excel Discussion (Misc queries) |