Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Truncation of string problem
Good day all.
I am having a problem with the creation of a SQL string. I am attempting to upload rows of data to an Access database and need a SQL command in the following format: insert into tablename (column1name,column2name...columnxname) values (value1,value2...valuex); The header row (ColumnNames) of my Excel sheet contains the following fields: resultsFields = " (" & "Date," & "Auditor," & "Shift," & "Product Grade," & "Ticket #," & "Product #," _ & "Ticket Pieces," & "Ticket Sq Footage," & "Broken Corner," & "Broke Edge," _ & "Splintered Edge," & "Min/Stain," & "End Overwood," & "Side Overwood," & "Delam," _ & "Bleed Back," & "Brush Marks," & "Stain Marks," & "Light/Dark Bevels," _ & "Blisters/Bubbles," & "Trash in Finish," & "Stain/Filler Pop," & "Stain Skips," _ & "UV Lines," & "Roller Marks," & "UV Skips," & "Indents," & "Shelly Grain/Shake," _ & "Splits in Veneer," & "Open Checks," & "Hull Out," & "Open Grain," & "White Grain," _ & "Burnt Bevel/ Edge," & "Bevel Size," & "Sand Dip," & "Moulder Line," & "Sanding Line," _ & "Chatter," & "Off Square," & "Sander Burn," & "Crush Groove," & "Raised Checks," _ & "Knots," & "Worm Holes," & "Pin Holes," & "End Lift," & "Bowed," & "Narrow End," _ & "Core Void," & "Sweep," & "Bark Pocket," & "Misc. / Other," & "Ticket Pcs out of spec," _ & "Ticket % out of spec," & "Comments)" I have also attempted to store the fields without concatenation. The problem that I have is the resultsFields variable only contains: " (Date,Auditor,Shift,Product Grade,Ticket #,Product #,Ticket Pieces,Ticket Sq Footage,Broken Corner,Broke Edge,Splintered Edge,Min/Stain,End Overwood,Side Overwood,Delam,Bleed Back,Brush Marks,Stain Marks,Light/Dark Bevels,Blisters/Bubbles,Trash in Fl" The String data type is states it can handle up to 2^31 characters: There are two kinds of strings: variable-length and fixed-length strings. A variable-length string can contain up to approximately 2 billion (2^31) characters. A fixed-length string can contain 1 to approximately 64K (2^16) characters. I hope someone can provide me insight to my problem. Thank you in advance for your assistance. Sincerely, Tony D. Abel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Truncation of string problem
Strings can certainly hold that amount of text. I assume you have
Dim resultsFields As String From the code you have posted there is no reason for the truncation; something else must be happening. As an aside, I would avoid using spaces, % and other characters apart from an underscore "_" in field names. Whilst this is allowed in Access, other DBs, engines and providers may have problems with them. NickHK "tdabel" wrote in message ... Good day all. I am having a problem with the creation of a SQL string. I am attempting to upload rows of data to an Access database and need a SQL command in the following format: insert into tablename (column1name,column2name...columnxname) values (value1,value2...valuex); The header row (ColumnNames) of my Excel sheet contains the following fields: resultsFields = " (" & "Date," & "Auditor," & "Shift," & "Product Grade," & "Ticket #," & "Product #," _ & "Ticket Pieces," & "Ticket Sq Footage," & "Broken Corner," & "Broke Edge," _ & "Splintered Edge," & "Min/Stain," & "End Overwood," & "Side Overwood," & "Delam," _ & "Bleed Back," & "Brush Marks," & "Stain Marks," & "Light/Dark Bevels," _ & "Blisters/Bubbles," & "Trash in Finish," & "Stain/Filler Pop," & "Stain Skips," _ & "UV Lines," & "Roller Marks," & "UV Skips," & "Indents," & "Shelly Grain/Shake," _ & "Splits in Veneer," & "Open Checks," & "Hull Out," & "Open Grain," & "White Grain," _ & "Burnt Bevel/ Edge," & "Bevel Size," & "Sand Dip," & "Moulder Line," & "Sanding Line," _ & "Chatter," & "Off Square," & "Sander Burn," & "Crush Groove," & "Raised Checks," _ & "Knots," & "Worm Holes," & "Pin Holes," & "End Lift," & "Bowed," & "Narrow End," _ & "Core Void," & "Sweep," & "Bark Pocket," & "Misc. / Other," & "Ticket Pcs out of spec," _ & "Ticket % out of spec," & "Comments)" I have also attempted to store the fields without concatenation. The problem that I have is the resultsFields variable only contains: " (Date,Auditor,Shift,Product Grade,Ticket #,Product #,Ticket Pieces,Ticket Sq Footage,Broken Corner,Broke Edge,Splintered Edge,Min/Stain,End Overwood,Side Overwood,Delam,Bleed Back,Brush Marks,Stain Marks,Light/Dark Bevels,Blisters/Bubbles,Trash in Fl" The String data type is states it can handle up to 2^31 characters: There are two kinds of strings: variable-length and fixed-length strings. A variable-length string can contain up to approximately 2 billion (2^31) characters. A fixed-length string can contain 1 to approximately 64K (2^16) characters. I hope someone can provide me insight to my problem. Thank you in advance for your assistance. Sincerely, Tony D. Abel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Truncation of string problem
If you want all the ColumnNames, you can simplify building the SQL string
with: Debug.Print Join(Application.Transpose(Application.Transpose(R ange("A1:M1"))), ", ") Similarly for your values, assuming they come from a comparable row of cells and do not require delimiters of "" or ##. e.g Dim SQLStr As String Const TableName As String = "YourTable" SQLStr = "INSERT INTO " & TableName SQLStr = SQLStr & " (" & Join(Application.Transpose(Application.Transpose(R ange("A1:M1"))), ", ") SQLStr = SQLStr & ") VALUES(" & Join(Application.Transpose(Application.Transpose(R ange("A3:M3"))), ", ") SQLStr = SQLStr & ")" NickHK "tdabel" wrote in message ... Good day all. I am having a problem with the creation of a SQL string. I am attempting to upload rows of data to an Access database and need a SQL command in the following format: insert into tablename (column1name,column2name...columnxname) values (value1,value2...valuex); The header row (ColumnNames) of my Excel sheet contains the following fields: resultsFields = " (" & "Date," & "Auditor," & "Shift," & "Product Grade," & "Ticket #," & "Product #," _ & "Ticket Pieces," & "Ticket Sq Footage," & "Broken Corner," & "Broke Edge," _ & "Splintered Edge," & "Min/Stain," & "End Overwood," & "Side Overwood," & "Delam," _ & "Bleed Back," & "Brush Marks," & "Stain Marks," & "Light/Dark Bevels," _ & "Blisters/Bubbles," & "Trash in Finish," & "Stain/Filler Pop," & "Stain Skips," _ & "UV Lines," & "Roller Marks," & "UV Skips," & "Indents," & "Shelly Grain/Shake," _ & "Splits in Veneer," & "Open Checks," & "Hull Out," & "Open Grain," & "White Grain," _ & "Burnt Bevel/ Edge," & "Bevel Size," & "Sand Dip," & "Moulder Line," & "Sanding Line," _ & "Chatter," & "Off Square," & "Sander Burn," & "Crush Groove," & "Raised Checks," _ & "Knots," & "Worm Holes," & "Pin Holes," & "End Lift," & "Bowed," & "Narrow End," _ & "Core Void," & "Sweep," & "Bark Pocket," & "Misc. / Other," & "Ticket Pcs out of spec," _ & "Ticket % out of spec," & "Comments)" I have also attempted to store the fields without concatenation. The problem that I have is the resultsFields variable only contains: " (Date,Auditor,Shift,Product Grade,Ticket #,Product #,Ticket Pieces,Ticket Sq Footage,Broken Corner,Broke Edge,Splintered Edge,Min/Stain,End Overwood,Side Overwood,Delam,Bleed Back,Brush Marks,Stain Marks,Light/Dark Bevels,Blisters/Bubbles,Trash in Fl" The String data type is states it can handle up to 2^31 characters: There are two kinds of strings: variable-length and fixed-length strings. A variable-length string can contain up to approximately 2 billion (2^31) characters. A fixed-length string can contain 1 to approximately 64K (2^16) characters. I hope someone can provide me insight to my problem. Thank you in advance for your assistance. Sincerely, Tony D. Abel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Truncation of string problem
Good day Nick.
I have solved my problem with a less elegant solution but it works. I will try your suggestion on my next project. Again good sir, I thank you for your response. Sincerely, Tony D. Abel "NickHK" wrote: If you want all the ColumnNames, you can simplify building the SQL string with: Debug.Print Join(Application.Transpose(Application.Transpose(R ange("A1:M1"))), ", ") Similarly for your values, assuming they come from a comparable row of cells and do not require delimiters of "" or ##. e.g Dim SQLStr As String Const TableName As String = "YourTable" SQLStr = "INSERT INTO " & TableName SQLStr = SQLStr & " (" & Join(Application.Transpose(Application.Transpose(R ange("A1:M1"))), ", ") SQLStr = SQLStr & ") VALUES(" & Join(Application.Transpose(Application.Transpose(R ange("A3:M3"))), ", ") SQLStr = SQLStr & ")" NickHK "tdabel" wrote in message ... Good day all. I am having a problem with the creation of a SQL string. I am attempting to upload rows of data to an Access database and need a SQL command in the following format: insert into tablename (column1name,column2name...columnxname) values (value1,value2...valuex); The header row (ColumnNames) of my Excel sheet contains the following fields: resultsFields = " (" & "Date," & "Auditor," & "Shift," & "Product Grade," & "Ticket #," & "Product #," _ & "Ticket Pieces," & "Ticket Sq Footage," & "Broken Corner," & "Broke Edge," _ & "Splintered Edge," & "Min/Stain," & "End Overwood," & "Side Overwood," & "Delam," _ & "Bleed Back," & "Brush Marks," & "Stain Marks," & "Light/Dark Bevels," _ & "Blisters/Bubbles," & "Trash in Finish," & "Stain/Filler Pop," & "Stain Skips," _ & "UV Lines," & "Roller Marks," & "UV Skips," & "Indents," & "Shelly Grain/Shake," _ & "Splits in Veneer," & "Open Checks," & "Hull Out," & "Open Grain," & "White Grain," _ & "Burnt Bevel/ Edge," & "Bevel Size," & "Sand Dip," & "Moulder Line," & "Sanding Line," _ & "Chatter," & "Off Square," & "Sander Burn," & "Crush Groove," & "Raised Checks," _ & "Knots," & "Worm Holes," & "Pin Holes," & "End Lift," & "Bowed," & "Narrow End," _ & "Core Void," & "Sweep," & "Bark Pocket," & "Misc. / Other," & "Ticket Pcs out of spec," _ & "Ticket % out of spec," & "Comments)" I have also attempted to store the fields without concatenation. The problem that I have is the resultsFields variable only contains: " (Date,Auditor,Shift,Product Grade,Ticket #,Product #,Ticket Pieces,Ticket Sq Footage,Broken Corner,Broke Edge,Splintered Edge,Min/Stain,End Overwood,Side Overwood,Delam,Bleed Back,Brush Marks,Stain Marks,Light/Dark Bevels,Blisters/Bubbles,Trash in Fl" The String data type is states it can handle up to 2^31 characters: There are two kinds of strings: variable-length and fixed-length strings. A variable-length string can contain up to approximately 2 billion (2^31) characters. A fixed-length string can contain 1 to approximately 64K (2^16) characters. I hope someone can provide me insight to my problem. Thank you in advance for your assistance. Sincerely, Tony D. Abel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table sourcedata string truncation | Excel Programming | |||
truncation problem | Excel Programming | |||
truncation problem | Excel Programming | |||
Copy range content as picture - truncation problem | Excel Programming | |||
string truncation | Excel Programming |