Re: Excel file transfer with SQL files?

["Peter Dow (ML)" <maillist@xxxxxxxxxxxxxxx>]

For what it's worth, here's a VBA macro to convert selected cells to 
character:

Sub Convert_nbr_to_txt()
   For Each c In Selection
       c.Value = "'" & Format(c.Value)
   Next c
   MsgBox "Numbers converted to text in selection."
End Sub

It basically prefixes the cell contents with an apostrophe (').

If you haven't used macros in Excel, go to Tools, Macros, Visual Basic 
Editor (or just press alt-F11) and copy/paste the above code into a 
module, then close it.  To use the macro, select the numeric cells, 
press alt-F8 and click on "Convert_nbr_to_txt" then Run.

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Jerry Adams wrote:
> I'm not sure about the VarChar() part, Loyd, but I had a similar problem with numeric data in the spreadsheet.  Somewhere in the transfer process is a data definition section, in which you can specify the data type for each column.  Setting the "numeric" column to a type of character worked for me.
>
> Jerry C. Adams
> IBM System i Programmer/Analyst
> B&W Wholesale
> office: 615-995-7024
> email:  jerry@xxxxxxxxxxxxxxx
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of lgoodbar@xxxxxxxxxxxxxx
> Sent: Thursday, July 24, 2008 2:03 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Excel file transfer with SQL files?
>
> Can the Excel file transfer (upload) utility be used with SQL-defined
> files?
>
> There's some data I would like to upload from Excel into a SQL file but
> am receiving errors.
>
>
>
> Added column headings to the data matching the system column names.
> Highlighted the data and column headings; they are in the same order as
> the file. Ran the "Transfer data to iSeries" in Excel. Create new
> request. Selected the option to Allow numeric data in character columns
> to be converted to character data.
>
>
>
> Receiving the error message "CWBTF0073 - The type of a column in the
> spreadsheet selection does not match the corresponding column type in
> the database file." It's too bad it doesn't specify which column is in
> error, how do I find this?
>
>
>
> The SQL table contains four columns, all are varchar(). I have "numbers"
> in Excel that should be treated as character data (cost centers).
>
>
>
> TIA,
>
> Loyd
>
>
>
> Loyd Goodbar
>
> Business Systems
>
> BorgWarner Shared Services
>
> 662-473-5713
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>