Import New Part 3's

I have a list of over 800 new parts that need to be entered into Visual 7.0.

Well, actually we are renumbering our parts. So, I've exported all of the Part IDs and need to load them back into the system with the same information. We've decided to handle the Engineering Masters manually, since there will be so many changes.

Has anyone done this before? Can you provide any advice or a sample SQL script?

I am particularly concerned with the long description (Part_Bits). Is there a good way to convert this to binary format for import?

Any guidance is appreciated.

0712131256598360.doc

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

ztrue

I know you can convert from the binary like this "convert(varchar(max), convert(varbinary(max), dbo.part_binary.bits))"

You may be able to reverse the converts into something, but I am not familiar with the SQL data construct of images to know whether this is feasible.

unknown

I would suggest you look into the Rename & Merge utility by Truefit Innovations:

www.truefitinnovation.com/visual-custom

"Rename & Merge for VISUAL...So whether you are looking to change part numbering schemes, rename parts for production, need to de-dupe customer information, adjust a charge of accounts, or have grown tired of tracking the same part in five different locations when it is only stored in one, Rename and Merge will be your new best friend in the database world...."

Truefit has been very closely linked to Visual, they are reliable and actually have many very interesting customizations and utilities. (I don't work for them...)

unknown

Valerie,
How did you export the current part ID's? Do you have someone who knows SQL? If not it is kind of a dicey proposition to run a SQL script to do the import. And I always run tests in a copy of the database first because it is so easy to make a mistake when writing a script.
That being said, here is a small piece of an example of a script just to give you an idea. This is one way to write the script, there are others. I did not include all the fields in the PART table since there are so many. This would insert a new record for part id 123456-7 and it loads each piece of information in order into the fields in the PART table (where it says 'and so on' you would have to have all the other PART field data in order - I'm not going to do it since there are almost 300 fields in the PART table):

insert into PART values ('123456-7','WIDGET','EA','0','D',NULL,NULL,NULL, NULL,NULL,NULL,'FIN', NULL, NULL,NULL,NULL,'Y','N’, ....and so on)

If you exported the data with SQL and put in into Excel then all your fields would hopefully already be in order. In theory you could just enter the new part id, create the script from the data in Excel and upload it. It would still be a bit of a pain but if you had someone who know SQL it would be faster than entering the parts manually.

As noted in other posts you also have to consider things like binary fields; we use a CAST statement for that, for example:
(CAST(BITS AS VARBINARY(8000)) AS VARCHAR(8000))

The other thing you have to consider is what data do you have that is in the Part Maintenance window that is not in the PART table, such as pricing and warehouses. Then figure out how you are going to load that info as well.

asa-andersson

Thanks for the Rename & Merge suggestion. This solves a LOT of the import issues, the biggest one being the ability to maintain part history. I am just waiting on approval from Finance.

In the meantime, I am trying to work on my update statements for the Part Binary table, but I am having difficulty. We use the long description for specific product info. What I need to do is update that description. I've got the new long descriptions in string format, but I need to convert them to binary prior to or during the update statement.

Here is where I am right now:

UPDATE PART_BINARY SET CAST(BITS AS VARBINARY(8000))='XXXXX' WHERE PART_ID='YYYYY';

However, I am getting this error: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

Does anyone have any suggestions to correct this syntax?

unknown

you have a double )) on the end, take one off

unknown

Or, try putting a space after the ) and = maybe ..

asa-andersson

I finally figured it out:

UPDATE PART_BINARY SET BITS=CAST('XXXXXX' AS VARBINARY(8000)) WHERE PART_ID='YYYYY';

Now, if only I could figure out how to interpret and include the carriage returns from the text.

ztrue

Use string concatenation and insert char(13)
'XXXXXX'
turns into
'' & char(13) & ''

May be "+" instead of "&" (I can never remember which SQL uses for string concatenation).

asa-andersson

I've tried that, but when I run the import it does not include the carriage returns. I wonder if this has to do with the cast to convert to binary.

0911060553270289.pdf

ztrue

Try char(10) or a combination of char(13) and char(10).

asa-andersson

Still no luck.

unknown

As an SQL query I've had success with:

update PART_BINARY set
BITS=('LINE1'+ CHAR(13)+CHAR(10) +'LINE2'),
BITS_LENGTH=LEN('LINE1'+ CHAR(13)+CHAR(10) +'LINE2')
where
PART_ID = 'YYYYYYYYY'
and TYPE='D'

The TYPE is there since I assume "D" means description.
Setting the length to the proper value is good measure, although I've never tried it without setting the length.

asa-andersson

I will play around with this, but I am not sure how it will work with the cast statement to convert to binary.

Copyright © 2025 Infor. All rights reserved.