Preserving white space when importing and updating.

[W Pearce]

I am creating a program that first reads each line of a fixed-length field document. Each line is a record, and depending on which character you are at, the values mean different things (for example, characters 1 through 20 may be an identification number). I am running into a problem when I load these through the JDBCODBC driver into SQL Server where it is dropping the white spaces. This means that when I am parsing the data, if a field has 3 characters instead of the 4 designated to it, all the fields following that will be off by one characters.

For example:
222 333 444 would be parsed as 222, 333, 444 (fields separated by commas)
but
22 333 444 would be parsed as 22, 334, 44

Is there a way I can force white space to stay?

[Scott Selikoff]

Can you post the code you using? Nothing in JDBC should be dropping white spaces, although its possible this may be a driver issue. I'd also consult the driver documentation to see if there are any parameters you can pass into the connection string related to white space handling.

[W Pearce]

public boolean load()
    {
        // load lines from readFile into table
        BufferedReader reader = null;
        try
        {
            reader = new BufferedReader(new FileReader(readFile));
            String line = "";

            while((line = reader.readLine()) != null)
            {
                // create a statement to update the correct table
                String stmt = "";
                String[] temp = line.split("'");
                line = "";
                for(int i = 0; i < temp.length; i++)
                {
                    line += temp[i];
                }
                
                if(line.startsWith("PAY"))
                {
                    stmt = "INSERT INTO " + names[0] + " VALUES (";
                    stmt = stmt + "'" + line + "');";
                    rawPAYLoaded++;
                    totalRawLoaded++;
                }
                else if(line.startsWith("CLM"))
                {
                    stmt = "INSERT INTO " + names[1] + " VALUES (";
                    stmt = stmt + "'" + line + "');";
                    rawCLMLoaded++;
                    totalRawLoaded++;
                }
                else if(line.startsWith("CTL"))
                {
                    stmt = "INSERT INTO " + names[2] + " VALUES (";
                    stmt = stmt + "'" + line + "');";
                    rawCTLLoaded++;
                    totalRawLoaded++;
                }

                // create an update to the appropriate table
                if(!stmt.equals(""))
                {
                    int i = sqlDriver.processUpdate(stmt);
                    // maybe add error circumstance here
                }
            }

            reader.close();
            return true;
        }
        catch(FileNotFoundException fnfe)
        {
            JOptionPane.showMessageDialog(null, "File unable to be found.", "Error",
                    JOptionPane.ERROR_MESSAGE);
            return false;
        }
        catch(IOException ioe)
        {
            JOptionPane.showMessageDialog(null, "Error trying to load data into raw tables.",
                        "Error", JOptionPane.ERROR_MESSAGE);
            return false;
        }
    }

[Jan Cumps]

Hi,

the code and the problem you have in your first post don't seem to relate.
We can see that you are splitting strings separated by ' , and putting the results in three tables based upon the starting sequence of the split.

But where is the problem with spaces happening?

[W Pearce]

Wow. I can't believe I overlooked something like this. Every time I split the string on an apostrophe, it cuts that character out, therefore when a field had a description with a possessive noun it was removing those characters. I have it replacing the characters with a space now and we're back up and running. I feel like such a schmuck!

[Jeanne Boyarsky]

Thanks for posting the solution.