+ Reply to Thread
Results 1 to 18 of 18

Excel to SQL Script

  1. #1
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Excel to SQL Script

    Hi,

    I already concatenate cell and put single quote, my problem is i have NULL value and I like to have a condition that all cell that have NULL value will not have a single quote.
    Example

    A B C
    1 Francis Lee NULL
    2 Lee Young 12345

    My Formula:
    =CONCATENATE("('", A1, "', '", B1, "', '", C1, "')")

    Output:
    ('Francis', 'Lee', 'NULL')

    My Desired Output:
    ('Francis', 'Lee', NULL)

    Thank you, badly need this.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel to SQL Script

    is it always as it appears? The output includes NULL? If so is NULL always in column C?
    if so you could use an IF statement like =IF(C1="NULL",CONCATENATE("('",A1,"', '",B1,"', ",C1,")"),CONCATENATE("('",A1,"', '",B1,"', '",C1,"')"))
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Excel to SQL Script

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel to SQL Script

    this one is a little shorter and a little cleaner...
    =IF(C1="NULL","("&"'"&A1&"', '"&B1&"', "&C1&")","("&"'"&A1&"', '"&B1&"', "&"'"&C1&"'"&")")

  5. #5
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    IT WORKS!!!!! Thank you so much for your help. I'll just figure out the rest of the cell! :')

  6. #6
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    Quote Originally Posted by Sambo kid View Post
    this one is a little shorter and a little cleaner...
    =IF(C1="NULL","("&"'"&A1&"', '"&B1&"', "&C1&")","("&"'"&A1&"', '"&B1&"', "&"'"&C1&"'"&")")
    Thank you!!!!!!!

  7. #7
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    Quote Originally Posted by Pepe Le Mokko View Post
    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks
    Noted! will include it next time! Thank you for the reminder

  8. #8
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    Quote Originally Posted by Sambo kid View Post
    this one is a little shorter and a little cleaner...
    =IF(C1="NULL","("&"'"&A1&"', '"&B1&"', "&C1&")","("&"'"&A1&"', '"&B1&"', "&"'"&C1&"'"&")")
    It works! Yey!

  9. #9
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    Quote Originally Posted by Sambo kid View Post
    is it always as it appears? The output includes NULL? If so is NULL always in column C?
    if so you could use an IF statement like =IF(C1="NULL",CONCATENATE("('",A1,"', '",B1,"', ",C1,")"),CONCATENATE("('",A1,"', '",B1,"', '",C1,"')"))
    No, NULL are not always in column C. It can be on other columns also.

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel to SQL Script

    per post #9 (you don't have to quote posts, it is clutter and not necessary, it can be easier to just refer to post numbers) if that didn't take care of it for you then please follow the directions in the yellow banner at the top of the post and upload a sample with representative data so we can better assist you.

  11. #11
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    Quote Originally Posted by Sambo kid View Post
    is it always as it appears? The output includes NULL? If so is NULL always in column C?
    if so you could use an IF statement like =IF(C1="NULL",CONCATENATE("('",A1,"', '",B1,"', ",C1,")"),CONCATENATE("('",A1,"', '",B1,"', '",C1,"')"))
    It can be also be on multiple columns also. I just try the code, it need to be in a specific column, my problem now is the other columns with NULL values. Thank you so much for your help. Figuring this out for 8 hours now

  12. #12
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    Quote Originally Posted by Sambo kid View Post
    per post #9 (you don't have to quote posts, it is clutter and not necessary, it can be easier to just refer to post numbers) if that didn't take care of it for you then please follow the directions in the yellow banner at the top of the post and upload a sample with representative data so we can better assist you.
    Sure, i'll put an attached file. Thank you

  13. #13
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    post #10, sorry

  14. #14
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    post #10 , here is the sample excel file. please see attached. thank you
    Attached Files Attached Files

  15. #15
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel to SQL Script

    in your posted example you have NULL in columns I and Z, can it be anywhere else too? If so a formula will not likely be the answer, more likely VBA.
    What do the outputs in A5 and A6 represent?
    It would be helpful if you added your expected results somewhere.

  16. #16
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    post #15, I checked the data that needed to be converted, I saw that only columns I and Z have NULL values. Sorry for the inconvenience. Please see the attached for the updated excel file. Thank you so much for helping me.
    Attached Files Attached Files
    Last edited by viaranola; 04-01-2020 at 07:53 AM. Reason: misspelled

  17. #17
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Excel to SQL Script

    Please try with helper row

    A4:AL5

    =",'"&A2&"'"&B4

    A19:A20
    =SUBSTITUTE("("&MID(A4,2,9999)&")","'NULL'","NULL")
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    04-01-2020
    Location
    Philippines
    MS-Off Ver
    Win 10, Licensed and Updated Excel Version
    Posts
    12

    Re: Excel to SQL Script

    Quote Originally Posted by Bo_Ry View Post
    Please try with helper row

    A4:AL5

    =",'"&A2&"'"&B4

    A19:A20
    =SUBSTITUTE("("&MID(A4,2,9999)&")","'NULL'","NULL")
    This is also very helpful, Thanks a lot!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. script to run the macro from Vb script with out opening the excel file
    By chandanp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2021, 03:33 AM
  2. Need A Fast Script To Copy 80000 Cells But My Current Script Is Too Slow
    By Genus Max in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2019, 09:54 AM
  3. Execute VB Script/ SAP Script from Excel
    By ramkrishna1111 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2015, 01:54 PM
  4. Integrating Mainframe emulator script with excel's vba script
    By Himanshu Mishra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-07-2012, 10:30 AM
  5. Create VBA script to short, dedicated button in excel for script?
    By realized in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2009, 11:54 PM
  6. Script that edits the text printed on the button that runs the script
    By petalred in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2008, 02:41 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1